I'm using the following code to load variable with data
from a spreadsheet. This works fine if the data is
clearly text or numeric format, but fails when the data is
sort of a hybrid. In this case, we have cell contents
that include "01", "02", etc. When these are pasted into
the new sheet, they come over as 1, 2, etc. This is a
problem.

Is there any way to use this same logic, but have it paste
the data as text (e.g., with the preceeding zero)?

Thanks, Todd


For j = 1 To LotCount
myRange.Offset(j, ColStart).Resize(1, 1).Select
mytemp = myRange.Offset(j, ColStart).Resize(1, 1).Value
If mytemp 0 Then
mytemp2 = myRange.Offset(j, 0).Resize(1, 1).Value
' MsgBox "Lot # = " & mytemp2
' MsgBox "Bid = " & mytemp
myLot(j) = mytemp2
myBid(j) = mytemp

End If

Next j

You could format the cell as Text before you put the value in.

And I'm not sure where the value goes, but something like this:

with myrange.offset(x,y).resize(1,1)
.numberformat = "@"
.value = mytemp
end with

or you could prefix it with a single quote:

myrange.offset(x,y).resize(1,1).value = "'" & mytemp

"T. Jenkins" wrote:

I'm using the following code to load variable with data
from a spreadsheet. This works fine if the data is
clearly text or numeric format, but fails when the data is
sort of a hybrid. In this case, we have cell contents
that include "01", "02", etc. When these are pasted into
the new sheet, they come over as 1, 2, etc. This is a
problem.

Is there any way to use this same logic, but have it paste
the data as text (e.g., with the preceeding zero)?

Thanks, Todd

For j = 1 To LotCount
myRange.Offset(j, ColStart).Resize(1, 1).Select
mytemp = myRange.Offset(j, ColStart).Resize(1, 1).Value
If mytemp 0 Then
mytemp2 = myRange.Offset(j, 0).Resize(1, 1).Value
' MsgBox "Lot # = " & mytemp2
' MsgBox "Bid = " & mytemp
myLot(j) = mytemp2
myBid(j) = mytemp

End If

Next j

--

Dave Peterson