I am trying to convert a worksheet column of 5 digit numeric zipcodes to
text in order to keep leading zeros (1234 should be 01234). If I enter
"=Text(A1,"00###")" in the cell I want, it will convert cell A1. However if
I use the following VBA code, I get no leading zero:

ActiveSheet.Cells(intRow, IntCol+1) =
Application.WorksheetFunction.Text(ActiveSheet.Cel ls(intRow, intCol).Value
, "00###")

I tried the following code with the same results (no leading zero added):

ActiveSheet.Cells(intRow, IntCol+1) = Format(ActiveSheet.Cells(intRow,
intCol).Value, "00###")

Does anyone know how to use VBA to format text in a worksheet cell?

Thanks,
Bob Ward

Try on of these instead:

ActiveSheet.Range("A1").NumberFormat = "00000"
ActiveSheet.Cells(intRow, intCol).NumberFormat = "00000"

tod

-----Original Message-----
I am trying to convert a worksheet column of 5 digit
numeric zipcodes to
text in order to keep leading zeros (1234 should be
01234). If I enter
"=Text(A1,"00###")" in the cell I want, it will convert
cell A1. However if
I use the following VBA code, I get no leading zero:

ActiveSheet.Cells(intRow, IntCol+1) =
Application.WorksheetFunction.Text(ActiveSheet.Ce lls
(intRow, intCol).Value
, "00###")

I tried the following code with the same results (no
leading zero added):

ActiveSheet.Cells(intRow, IntCol+1) = Format
(ActiveSheet.Cells(intRow,
intCol).Value, "00###")

Does anyone know how to use VBA to format text in a
worksheet cell?

Thanks,
Bob Ward


.

ActiveSheet.Cells(intRow, IntCol+1) = Format(ActiveSheet.Cells(intRow,
intCol).Value, "00###")


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Bob Ward" wrote in message
...
I am trying to convert a worksheet column of 5 digit numeric zipcodes to
text in order to keep leading zeros (1234 should be 01234). If I enter
"=Text(A1,"00###")" in the cell I want, it will convert cell A1. However
if
I use the following VBA code, I get no leading zero:

ActiveSheet.Cells(intRow, IntCol+1) =
Application.WorksheetFunction.Text(ActiveSheet.Cel ls(intRow, intCol).Value
, "00###")

I tried the following code with the same results (no leading zero added):

ActiveSheet.Cells(intRow, IntCol+1) = Format(ActiveSheet.Cells(intRow,
intCol).Value, "00###")

Does anyone know how to use VBA to format text in a worksheet cell?

Thanks,
Bob Ward