vba converts text to values?

[bill_s1416]

Need help with vba in Excel. I have a list of zip codes many of which begin with zeroes so I have converted them to text using Excel's built-in Text function: Text(ZipCode,"00000"). When I run the vba command to assign the value of a zip to another cell on the spreadsheet, it converts it to a number, not as text, and thus my routine bombs on subsequent lines of code on a "type mismatch."

ex: Range("NewZip").Value = Range("OldZip").Value

where OldZip is the original zip code formatted as text and NewZip is the cell where the value of the zip code is assigned--with the leading zeroes stripped off. Is there some sort of Text command in vba whereby I could do something like:

Range("NewZip").Value = Text(Range("OldZip).Value,"0000")

you know, kind of like the built-in Excel text command?

[mudraker]

Bill


You can overcome this problem several ways

Method 1

Range("NewZip").NumberFormat = "@"
Range("NewZip").Value = Text(Range("OldZip).Value,"0000")


Method 2

Range("NewZip").Value = "'" & Text(Range("OldZip).Value,"0000")