VBA Dynamic Array converts text to numbers?

[magemaester]

Hi forum,


I have this piece of code which creates a dynamic array, redims it, then assigns a range of cells on a worksheet into it. The data that I populate into the array are ID's of people, that are formatted as TEXT with leading 0's. (e.g. "000123123", "000111223", "000199382".....)

[COLOR=#006400]'Creates array[/COLOR]
Dim Array() as Variant
ReDim Array (1 to 1000, 1)

[COLOR=#006400]'Populates array with TEXT from Column A[/COLOR]
Array = Range("A1:A1000").Value

[COLOR=#006400]'Pastes data from array into Column B[/COLOR]
Range("B1:B1000").Value = Array

However, when the values are pasted into Column B, all the leading "0"s disappear and the output is formatted as numbers. Does anyone know why that is happening/solution?

Many thanks.

 

[JoeMo]

Excel works hard to convert anything resembling a number to a number. Try it like this:

Sub ArrToB()
Dim Arr As Variant
Arr = Range("A1:A1000").Value
With Range("B1:B1000")
    .NumberFormat = "000######"
    .Value = Arr
End With
End Sub

BTW notice I used Arr as my variant not Array. The latter is the name of a VBA function and is likely to get you into trouble if used for anything else.

 

[Rick Rothstein]

.NumberFormat = "000######"

Why did you use that number format instead of "000000000"? The reason I ask is, with your format, if the number in the cell less than 6 digits long, that number would be prefixed with only three zeroes. For example, if the number were 123, you format would produce 000123 whereas I would expect the OP would want 000000123.

 

[Peter_SSs]

Since your original data is Text, why not have your new data in the same format? A couple of ways ..
a) Pre-format column B as Text and your original code will work (after you fix the variable name 'Array' as mentioned by JoeMo), or
b) Use code like JoMoe's but have the code apply Text format instead of a Number format. That would preserve the original IDs no matter how many characters or leading zeroes it has.

So the last bit of the code would be

With Range("B1:B1000")
  .NumberFormat = "@"
  .Value = Arr
End With

BTW, there is no need for the ReDim in your code. When you read a range into the array, that redimensioning will happen automatically.

 

[JoeMo]

Why did you use that number format instead of "000000000"? The reason I ask is, with your format, if the number in the cell less than 6 digits long, that number would be prefixed with only three zeroes. For example, if the number were 123, you format would produce 000123 whereas I would expect the OP would want 000000123.

I assumed all data would have the same format as the OP's examples. But, I think Peter has offered a better solution in post #4 .