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

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
----------------------------------------------------------------

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

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"
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
----------------------------------------------------------------

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

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
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"fbagirov" wrote:

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
----------------------------------------------------------------