keep leading zeros copy array to range

[nikaleya]

Hi community,

to speed up my code and prevent any disruptions I "replaced" the find&replace-function with the idea to first copy the affected range to an array, substitute the "vfind" with the "rplc" and copy the array back to the range.

'set array
Dim myarray() As Variant
myarray = lastyear.Range("M4:M" & lastrow) 'two dimensional !!!

'find & replace
Dim i, j As Variant
For i = LBound(myarray) To UBound(myarray)
    For j = 1 To 1
    myarray(i, j) = WorksheetFunction.Substitute(myarray(i, j), vfind, rplc)
    Next j
Next i

'copy array to range
lastyear.Range("M4:M" & lastrow) = myarray

This works fine. Unfortunately, the range contains numbers stored as text (like "002.2021", for dates). The values in the array are correct, but when they get copied back to the range, the leading zeros are gone.
Any idea how I to keep them? Its important to keep the dates in this format as it is the global format for our reporting.

Thanks for help & thoughts!

 

[Alex Blakenburg]

Try putting this (formattin the output range as text) before your last statement putting the array back.

lastyear.Range("M4:M" & lastrow).NumberFormat = "@"

Alternatively format the whole column as text.

 

[nikaleya]

Try putting this (formattin the output range as text) before your last statement putting the array back.

lastyear.Range("M4:M" & lastrow).NumberFormat = "@"

Alternatively format the whole column as text.

Hi Alex,

thanks for your fast response, works perfectly!

 

[Alex Blakenburg]

Great to hear, glad I could help.