Code verkorten

[rvt1982]

Goedemorgen,

Ik heb de volgende script, echter weet ik dat het korter en beter kan... alleen weet ik niet hoe...

Wie kan mij hier mee helpen ?

Alvast bedankt voor de moeite

With Workbooks("Excel.xls").Sheets("Zoeken")
.Range("ac2") = .Range("ac2") & ", " & Format(.Range("aa2"), "dd/mm/yyyy") & ", " & .Range("ab2")
.Range("ac3") = .Range("ac3") & ", " & Format(.Range("aa3"), "dd/mm/yyyy") & ", " & .Range("ab3")
.Range("ac4") = .Range("ac4") & ", " & Format(.Range("aa4"), "dd/mm/yyyy") & ", " & .Range("ab4")
.Range("ac5") = .Range("ac5") & ", " & Format(.Range("aa5"), "dd/mm/yyyy") & ", " & .Range("ab5")
.Range("ac6") = .Range("ac6") & ", " & Format(.Range("aa6"), "dd/mm/yyyy") & ", " & .Range("ab6")
.Range("ac7") = .Range("ac7") & ", " & Format(.Range("aa7"), "dd/mm/yyyy") & ", " & .Range("ab7")
.Range("ac8") = .Range("ac8") & ", " & Format(.Range("aa8"), "dd/mm/yyyy") & ", " & .Range("ab8")
.Range("ac9") = .Range("ac9") & ", " & Format(.Range("aa9"), "dd/mm/yyyy") & ", " & .Range("ab9")
.Range("ac10") = .Range("ac10") & ", " & Format(.Range("aa10"), "dd/mm/yyyy") & ", " & .Range("ab10")
.Range("ac11") = .Range("ac11") & ", " & Format(.Range("aa11"), "dd/mm/yyyy") & ", " & .Range("ab11")
.Range("ac12") = .Range("ac12") & ", " & Format(.Range("aa12"), "dd/mm/yyyy") & ", " & .Range("ab12")
.Range("ac13") = .Range("ac13") & ", " & Format(.Range("aa13"), "dd/mm/yyyy") & ", " & .Range("ab13")
.Range("ac14") = .Range("ac14") & ", " & Format(.Range("aa14"), "dd/mm/yyyy") & ", " & .Range("ab14")
.Range("ac15") = .Range("ac15") & ", " & Format(.Range("aa15"), "dd/mm/yyyy") & ", " & .Range("ab15")
.Range("ac16") = .Range("ac16") & ", " & Format(.Range("aa16"), "dd/mm/yyyy") & ", " & .Range("ab16")
.Range("ac17") = .Range("ac17") & ", " & Format(.Range("aa17"), "dd/mm/yyyy") & ", " & .Range("ab17")
.Range("ac18") = .Range("ac18") & ", " & Format(.Range("aa18"), "dd/mm/yyyy") & ", " & .Range("ab18")
.Range("ac19") = .Range("ac19") & ", " & Format(.Range("aa19"), "dd/mm/yyyy") & ", " & .Range("ab19")
.Range("ac20") = .Range("ac20") & ", " & Format(.Range("aa20"), "dd/mm/yyyy") & ", " & .Range("ab20")
.Range("ac21") = .Range("ac21") & ", " & Format(.Range("aa21"), "dd/mm/yyyy") & ", " & .Range("ab21")
.Range("ac22") = .Range("ac22") & ", " & Format(.Range("aa22"), "dd/mm/yyyy") & ", " & .Range("ab22")
.Range("ac23") = .Range("ac23") & ", " & Format(.Range("aa23"), "dd/mm/yyyy") & ", " & .Range("ab23")
.Range("ac24") = .Range("ac24") & ", " & Format(.Range("aa24"), "dd/mm/yyyy") & ", " & .Range("ab24")
.Range("ac25") = .Range("ac25") & ", " & Format(.Range("aa25"), "dd/mm/yyyy") & ", " & .Range("ab25")
.Range("ac26") = .Range("ac26") & ", " & Format(.Range("aa26"), "dd/mm/yyyy") & ", " & .Range("ab26")
.Range("ac27") = .Range("ac27") & ", " & Format(.Range("aa27"), "dd/mm/yyyy") & ", " & .Range("ab27")
End With

 

[edmoor]

Even snel. Probeer dit eens:

With Workbooks("Excel.xls").Sheets("Zoeken")
    For i = 2 to 27
       .Range("ac" & i) = .Range("ac" & i) & ", " & Format(.Range("aa" & i), "dd/mm/yyyy") & ", " & .Range("ab" & i)
    Next i
End With

 

[snb]

of

Workbooks("Excel.xls").Sheets("Zoeken").Range("AC:AC27")=[index(AC2:AC27 & ", " & Text(AA2:AA27, "dd/mm/yyyy") & ", " & AB2:AB27,)]

 

[rvt1982]

Thanks Edmoor & snb,

Dit is wat ik zocht, mijn dank is weer groot :thumb:

 

[snb]

Dat kan toch korter ?

Workbooks("Excel.xls").Sheets("Zoeken").range("AC:AC27")=[index(AC2:AC27&Text(AA2:AA27, ", dd/mm/yyyy, ")&AB2:AB27,)]