variant/string vs. variant/double

[ryoun1b]

I am moving a text value from one worksheet in an Excel 2002 workbook to another worksheet and have placed a watchpoint in my code for the values of the cells. The source value is '06071' where the number is a valid text entry, while the target value is '6071' where the zero was dropped because VB interpreted this as a number not as a text field as intended.

The code snippet looks like the following:

Worksheets(Importfilename).Cells(entryrow, 2).Value = Str(Worksheets("Entry&quot.Cells(4, 2).Value) 'Comm ID

I added the STR() function to force the value on the worksheet "Importfilename" to be a text value but instead it drops the zero and is a number value.

How can I make the target a string value and keep the source text value of '06071'?

Thanks!

 

[Zathras]

This should do the trick:
[blue]

With Worksheets(Importfilename).Cells(entryrow, 2)
 .NumberFormat = "@"
 .Value = Worksheets("Entry").Cells(4, 2).Text 'Comm ID
End With

[/color]

 

[SkipVought]

I have foud that this works best with text...

Worksheets(Importfilename).Cells(entryrow, 2).Value = "'" & Worksheets("Entry").Cells(4, 2).Value

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[TonyJollans]

Hi ryoun1b,

You say ... VB interpreted this as a number not as a text field as intended. So you have text in one cell and a number in the other.

The Str function will not add leading zeroes for you (it doesn't know how many you want). You could try converting the text cell to a number rather than the other way round:

Val(Worksheets(Importfilename).Cells(entryrow, 2).Value) = Worksheets("Entry").Cells(4, 2).Value 'Comm ID

But be aware of what Val does - it should be OK for your example but not necessarily everything else - see the Help for full details. If it doesn't suit, one of the Type conversion functions such as CLng might do.

A more clumsy, but reasonably bullet-proof, solution is to add the leading zeroes yourself. If the above doesn't suit and you have trouble post back and I'll put together the statement for you.

Enjoy,
Tony