Range text vs Range value

[paqogomez]

I have an excel file that is filled with zero padded numbers. If I
pull this info from the cell level with the Text method, (eg.
cell('C1').Text) I get '0002124'.

What i want however is a range (eg. range('A2', xlLastCell)). This
gives me values like 2124.0 instead of '0002124'. I know that
range('A2', xlLastCell).Text doesnt work, is there anything that does?

TIA
paqo

 

[Dave Peterson]

I think you're going to have to loop through those cells to pick up the .text.

Option Explicit
Sub testme()
Dim myRng As Range
Dim iRow As Long
Dim iCol As Long
Dim myArr() As Variant

With ActiveSheet
Set myRng = .Range("a2:b9")
End With

ReDim myArr(1 To myRng.Rows.Count, 1 To myRng.Columns.Count)

For iRow = 1 To myRng.Rows.Count
For iCol = 1 To myRng.Columns.Count
myArr(iRow, iCol) = myRng(iRow, iCol).Text
Next iCol
Next iRow

End Sub