Add leading zero to cell

[BexleyManor]

Ok, I can't for the life of me work out why this doesn't add a leading zero to the data in the cell?? :dunno

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "@"
For Each Cell In Selection
Cell.Value = Right("00000" & Cell.Value, Len(Cell.Value) + 0)
Next

I have both five & six digit numbers in the range but only want to add the 0 to cells with 5 digits. What am I doing wrong?? :doh:

Thanks folks!!

[Bob Phillips]

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "@"
For Each Cell In Selection
Cell.Value = Right("00000" & Cell.Value, 6)
Next


You could just format as 000000

[BexleyManor]

Thanks for your input XLD, unfortunately this didn't seem to work for me either??

Starting to pull hair now!!

[mdmackillop]

Can you post a sample?

[johnske]

Is this what you're trying to do? (you don't have 1,2,3,4 digit numbers?) Sub AddZero()
Dim Cell As Range
For Each Cell In Range("A1", Range("A1").End(xlDown).Address)
Cell.NumberFormat = "@"
If Len(Cell) = 5 Then Cell = "0" & Cell
Next
End Sub

EDIT: If you do have 1,2,3,4 digit numbers and want to make them all 6 digit, you could use a Do loop e.g.Sub AddZeroes()
Dim Cell As Range
For Each Cell In Range("A1", Range("A1").End(xlDown).Address)
Cell.NumberFormat = "@"
Do While Len(Cell) < 6
Cell = "0" & Cell
Loop
Next
End Sub

[mdmackillop]

I'd go with

Sub AddZeroes()
Range("A1", Range("A1").End(xlDown).Address).NumberFormat = "000000"
End Sub

[johnske]

:thumb Nice Malcolm, but I think he said zeroes were to be added to a list of pre-existing numbers, so (incorporating what you gave) you would still need to use something like
Sub AddZeroes1()
Dim Cell As Range
For Each Cell In Range("A1", Range("A1").End(xlDown).Address)
Cell.NumberFormat = "000000"
Cell = Cell
Next
End Sub

[mdmackillop]

Really?

[johnske]

Yes, I just did my own test, sorry, if the cells have already been formatted as text when the data was entered, that doesn't add any zeroes (and my variation that's based on yours fails also :))

[johnske]

But this next variation doesn't :)
Sub AddZeroes2()
With Range("A1", Range("A1").End(xlDown).Address)
.NumberFormat = "000000"
.Value = .Value
End With
End Sub

[mdmackillop]

Yeah, well if numbers are formatted as text I think a lot of our macros are going to fail. I'm guessing that this was a left over from previous attempts to add the leading zero, and would not normally be a problem.

[johnske]

... I'm guessing that this was a left over from previous attempts to add the leading zero, and would not normally be a problem.And I agree, I think there-in lies the crux of Bexleys peculiar problem :)