vba is converting text to values!

[Bill_S]

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?

[Vasant Nanavati]

Very close.

Range("NewZip").Value = Format(Range("OldZip).Value,"00000")

(I think you were missing a zero in your format.)

--

Vasant

"Bill_S" <[email protected]> wrote in message
news:[email protected]...
> 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?

[Stevie_mac]

Range("NewZip").NumberFormat = "@" 'Set to TEXT format 1st
Range("NewZip").Value = Range("OldZip").Value

"Bill_S" <[email protected]> wrote in message news:[email protected]...
> 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?