Variant array changes numbers stored as text

[bumbochaka]

Hello!

So I have this macro I made for work... its too irrelevant to explain the entire macro, so I will try to explain the part I have an issue with. A section of the macro copies values from a named range (Ex. "Color1") into an array that holds values temporarily from loop to loop (Ex. "ColorVT") and then that is copied into a final array (Ex. "FinalVT_Color") that is pasted into a column in a final output sheet once the macro runs through a bunch of loops to populate this "FinalVT_Color". My problem is that if I have a color number that was entered as '002 in excel (# as text so the first 0 doesnt fall off) within the named range "Color1" ... by the time the macro prints the "FinalVT_Color" color array it has changed to 2. That is a huge issue for me, what to do? Please help!!!! Thank you

Dim Colors As Integer
Dim ColorVT As Variant
Dim IdentifierVT As Variant
Dim BusinessUnitVT As Variant
Dim WarehouseVT As Variant

Dim ColorNumString As String
Dim IdentifierNumString As String
Dim BusinessUnitNumString As String
Dim WarehouseNumString As String

Dim k As Integer
Dim FinalVT_ArrayPosition As Long
Dim FinalVT_Color()
Dim FinalVT_Identifier()
Dim FinalVT_BusinessUnit()
Dim FinalVT_Warehouse()



For k = 1 To WeeksPerMonth '-------------------------------------------- LOOPS THROUGH EACH WEEK IN MONTH

ColorNumString = "Color" & CStr(i) '----------------------------------- "i" RANGES B/W 1 AND 10, OUTSIDE LOOP
Set ColorVT = WorkingPlan.Names(ColorNumString).RefersToRange
ReDim Preserve FinalVT_Color(FinalVT_ArrayPosition) '---------------------- "FinalVT_ArrayPosition" INTIALLY = 1
FinalVT_Color(FinalVT_ArrayPosition) = ColorVT(Colors) '---------- "Colors" RANGES B/W 1 AND 10, OUTSIDE LOOP

IdentifierNumString = "Identifier" & CStr(i)
Set IdentifierVT = WorkingPlan.Names(IdentifierNumString).RefersToRange
ReDim Preserve FinalVT_Identifier(FinalVT_ArrayPosition)
FinalVT_Identifier(FinalVT_ArrayPosition) = IdentifierVT(Colors)

BusinessUnitNumString = "BusinessUnit" & CStr(i)
Set BusinessUnitVT = WorkingPlan.Names(BusinessUnitNumString).RefersToRange
ReDim Preserve FinalVT_BusinessUnit(FinalVT_ArrayPosition)
FinalVT_BusinessUnit(FinalVT_ArrayPosition) = BusinessUnitVT(Colors)

WarehouseNumString = "Warehouse" & CStr(i)
Set WarehouseVT = WorkingPlan.Names(WarehouseNumString).RefersToRange
ReDim Preserve FinalVT_Warehouse(FinalVT_ArrayPosition)
FinalVT_Warehouse(FinalVT_ArrayPosition) = WarehouseVT(Colors)

FinalVT_ArrayPosition = FinalVT_ArrayPosition + 1

Next

.
.
.
.
.

WorkingPlan.Sheets("Vertical Table").Range("D2:D" & UBound(FinalVT_Color) + 1) _
= WorksheetFunction.Transpose(FinalVT_Color)

WorkingPlan.Sheets("Vertical Table").Range("E2:E" & UBound(FinalVT_Identifier) + 1) _
= WorksheetFunction.Transpose(FinalVT_Identifier)

WorkingPlan.Sheets("Vertical Table").Range("A2:A" & UBound(FinalVT_BusinessUnit) + 1) _
= WorksheetFunction.Transpose(FinalVT_BusinessUnit)

WorkingPlan.Sheets("Vertical Table").Range("F2:F" & UBound(FinalVT_Warehouse) + 1) _
= WorksheetFunction.Transpose(FinalVT_Warehouse)

[watersev]

hi bumbochaka, welcome to Excelforum, please check attachment, press Run button. I think the idea to overcome your issue is clear

[bumbochaka]

Thank you very much for taking the time to post this solution! Worked perfectly