Updating 0s in front of the number values in the column

[Guest]

Hello,

I've got a column with Zip Codes in 12345-1234 format, but some zipcodes are
in 12345 format. When the column is converted to text, those without dashes
loose 0 in front of it.

I wrote a macro to fix it. It suppose to check if the cell has less than 4
digits, and if so, add a 0 in front of it.

It's not working - what is wrong with it ? Thanks!


-------------------------------------------------
Sub ZipCodeFixer()
Dim Zip1 As String

Application.ScreenUpdating = False

'go to the first cell in the column
Cells(2, 16).Select
Do While Mid(ActiveCell, 5, 1) <> ""
If Mid(ActiveCell, 5, 1) = "" Then
Zip1 = Mid(ActiveCell, 1, 4)
ActiveCell.FormulaR1C1 = "'" + Zip1
End If
ActiveCell.Offset(1, 0).Select
Loop

Application.ScreenUpdating = True

End Sub
----------------------------------------------------------------

 

[Jim Cone]

This tries to keep as much as possible of your original approach ...

Sub ZipCodeFixer()
On Error GoTo xxx
Const Zip1 As String = "'0"
Application.ScreenUpdating = False

'go to the first cell in the column
Cells(2, 6).Select
Do
If Len(ActiveCell) = 4 Then
ActiveCell.Value = Zip1 & ActiveCell.Value
End If
ActiveCell.Offset(1, 0).Select
Loop While Len(ActiveCell) > 0

Application.ScreenUpdating = True
Exit Sub
xxx:
MsgBox Err.Number & " " & Err.Description
End Sub


"fbagirov" <[email protected]>
wrote in message
Hello,
I've got a column with Zip Codes in 12345-1234 format, but some zipcodes are
in 12345 format. When the column is converted to text, those without dashes
loose 0 in front of it.

I wrote a macro to fix it. It suppose to check if the cell has less than 4
digits, and if so, add a 0 in front of it.
It's not working - what is wrong with it ? Thanks!


-------------------------------------------------
Sub ZipCodeFixer()
Dim Zip1 As String

Application.ScreenUpdating = False

'go to the first cell in the column
Cells(2, 16).Select
Do While Mid(ActiveCell, 5, 1) <> ""
If Mid(ActiveCell, 5, 1) = "" Then
Zip1 = Mid(ActiveCell, 1, 4)
ActiveCell.FormulaR1C1 = "'" + Zip1
End If
ActiveCell.Offset(1, 0).Select
Loop

Application.ScreenUpdating = True

End Sub
----------------------------------------------------------------

 

[Guest]

Hi,

All you need to do is check the length of the string and then format it and
set the cell format to text (you can add the ' if you want in the format
string as "'00000"

I assume you stop on the blank cell.

Sub ZipCodeFixer()
Application.ScreenUpdating = False
'go to the first cell in the column [P2]
Cells(2, 16).Select
Do While ActiveCell <> ""
If Len(ActiveCell) < 5 Then
ActiveCell.NumberFormat = "@" ' as text
ActiveCell = Format(ActiveCell, "00000")
End If
ActiveCell.Offset(1, 0).Select
Loop
Application.ScreenUpdating = True
End Sub