Leading zeros

[Excelnewbie001]

I have the following code to add a leading zero to my data in column E1-E20 the problem is when I run this macro which is set to 3 digits it runs down 210 lines adding a 0 -it musnt only the data in column E -in this example the 4 should be 004 and the 40 should be a 400 it must leave all the data thats already in 3 numbers so it should only adress the leading zero -this code fix the leading zero but it doesnt fix the 40 adding a zero at the end -any help much appreciated as it needs tweaking
[TABLE="width: 115"]
<tbody>[TR]
[TD]Sample data in

Column E
4[/TD]
[/TR]
[TR]
[TD]22[/TD]
[/TR]
[TR]
[TD]40[/TD]
[/TR]
[TR]
[TD]13[/TD]
[/TR]
[TR]
[TD]301[/TD]
[/TR]
[TR]
[TD]310[/TD]
[/TR]
[TR]
[TD]211[/TD]
[/TR]
[TR]
[TD]220[/TD]
[/TR]
[TR]
[TD]130[/TD]
[/TR]
[TR]
[TD]121

This is the macro code

Sub LeadZ()
Dim Rng As Range
Dim rVal As String
Dim selCol As Range
Sheets("Test").Select
Range("E:E").Select


Set selCol = Application.Intersect(ActiveSheet.UsedRange, _
                    ActiveSheet.Columns(ActiveCell.Column))
    
  For Each Rng In selCol
    Rng.Select
    Selection.NumberFormat = "@"
    rVal = Rng.Value
    If Len(Rng.Value) < 3 Then
    Rng.Value = "0" & rVal
    End If
    
   Next
 
MsgBox "Process is complete."
End Sub

[/TD]
[/TR]
</tbody>[/TABLE]

 

[mole999]

rather than changing it, would it be worth viewing as three digit

 

[Excelnewbie001]

rather than changing it, would it be worth viewing as three digit

Thanks for your reply -yes it need to be 3 digits....I think I got it fixed by changing 0 to 00 but its still adding 00 down to line 246 -if I can only set the range fixed on E1-E20 -thank you

 

[mole999]

if you selected the column, then custom format as 000, then you would see what you want

 

[Excelnewbie001]

if you selected the column, then custom format as 000, then you would see what you want

yes but its still adding 000 246 lines down in column E -I dont want that to do that -only need that fixed if you can help please mole999

 

[MARK858]

yes but its still adding 000 246 lines down in column E -I dont want that to do that -only need that fixed if you can help please mole999

If you only want it to run from E1 to E20 why are you using Application.UsedRange in

Set selCol = Application.Intersect(ActiveSheet.UsedRange, _
                    ActiveSheet.Columns(ActiveCell.Column))

Do you really want the range fixed to E1 to E20 or do you actually want it to the last cell with a value in column E?

 

[Excelnewbie001]

If you only want it to run from E1 to E20 why are you using Application.UsedRange in

Set selCol = Application.Intersect(ActiveSheet.UsedRange, _
                    ActiveSheet.Columns(ActiveCell.Column))

Do you really want the range fixed to E1 to E20 or do you actually want it to the last cell with a value in column E?

Mark it must be only be the last data -that would be great. I have not written it I got the code from net trying to make it work

 

[MARK858]

What happens with...

Sub LeadZ()
Dim Rng As Range
Dim rVal As String
Dim selCol As Range

Set selCol = Sheets("Test").Range("E1:E" & Sheets("Test").Columns(5).Find("*", , xlValues, , xlRows, xlPrevious).Row)
    
  selCol.NumberFormat = "@"
  
  For Each Rng In selCol
  
    rVal = Rng.Value
    If Len(Rng.Value) < 3 Then
    Rng.Value = "00" & rVal
    End If
    
   Next

MsgBox "Process is complete."
End Sub

 

[Excelnewbie001]

What happens with...

Sub LeadZ()
Dim Rng As Range
Dim rVal As String
Dim selCol As Range

Set selCol = Sheets("Test").Range("E1:E" & Sheets("Test").Columns(5).Find("*", , xlValues, , xlRows, xlPrevious).Row)
    
  selCol.NumberFormat = "@"
  
  For Each Rng In selCol
  
    rVal = Rng.Value
    If Len(Rng.Value) < 3 Then
    Rng.Value = "00" & rVal
    End If
    
   Next

MsgBox "Process is complete."
End Sub

Marks thank you very much this solves it 100% -thank you great code!

 

[Rick Rothstein]

What happens with...

Sub LeadZ()
Dim Rng As Range
Dim rVal As String
Dim selCol As Range

Set selCol = Sheets("Test").Range("E1:E" & Sheets("Test").Columns(5).Find("*", , xlValues, , xlRows, xlPrevious).Row)
    
  selCol.NumberFormat = "@"
  
  For Each Rng In selCol
  
    rVal = Rng.Value
    If Len(Rng.Value) < 3 Then
    Rng.Value = "00" & rVal
    End If
    
   Next

MsgBox "Process is complete."
End Sub

Here is another way to write your LeadZ macro...

[table="width: 500"]
[tr]
	[td]Sub LeadZ()
  Dim Addr As String
  Addr = "E1:E" & Cells(Rows.Count, "E").End(xlUp).Row
  Range(Addr).NumberFormat = "@"
  Range(Addr) = Evaluate(Replace("IF(LEN(@)<3,""00""&@,@)", "@", Addr))
End Sub[/td]
[/tr]
[/table]