Formatting zipcodes as text in worksheet cells

  • Thread starter Thread starter Bob Ward
  • Start date Start date
B

Bob Ward

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.Cells(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
 
ActiveSheet.Cells(intRow, IntCol+1) = Format(ActiveSheet.Cells(intRow,
intCol).Value, "00###")


--

HTH

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