keep leading zeros copy array to range

nikaleya

New Member
Joined
Feb 15, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
'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!
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,686
Office Version
  1. 365
Platform
  1. Windows
Try putting this (formattin the output range as text) before your last statement putting the array back.

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

Alternatively format the whole column as text.
 
Solution

nikaleya

New Member
Joined
Feb 15, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Try putting this (formattin the output range as text) before your last statement putting the array back.

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

Alternatively format the whole column as text.

Hi Alex,

thanks for your fast response, works perfectly!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,399
Messages
5,769,849
Members
425,574
Latest member
grimeslisa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top