Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ---------------------------------------------------------------- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ---------------------------------------------------------------- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 ---------------------------------------------------------------- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How add a digit to front of number in each cell in excexl column | Excel Discussion (Misc queries) | |||
put 0 in front of number | Excel Discussion (Misc queries) | |||
Creating zero's in front of number and after number | Excel Discussion (Misc queries) | |||
Get number of different values in a column | Excel Programming | |||
zero in front of a number | Excel Discussion (Misc queries) |