macro to add leading zeroes to number and loop

[maijiuli]

Hello,

I have a data set of employee numbers. the numbers range from 1 - 6
characters and can be all numbers or have letters. I need to compare this to
another worksheet but need to convert the EE numbers to text and all must be
6 characters long. So I need to do the leading zeroes for all EE numbers.
The EE numbers are found on column E and the range can differ week to week so
I would also need to do a loop until there is nothing left in row E (not sure
how to do that one?).

I wish to have a macro so others can use too. Thanks for looking,

 

[Gary''s Student]

Sub fixum()
n = Cells(Rows.Count, "E").End(xlUp).Row
For i = 1 To n
v = Cells(i, "E")
l = Len(v)
If l = 6 Then
Else
v = Application.WorksheetFunction.Rept("0", 6 - l) & v
Cells(i, "E").NumberFormat = "@"
Cells(i, "E").Value = v
End If
Next
End Sub

 

[Bob Phillips]

Public Sub ProcessData()
Dim i As Long, j As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
For i = 2 To LastRow

If Len(.Cells(i, "E").Text) < 6 Then

.Cells(i, "E").Value = "'" & Left("00000", 6 - Len(.Cells(i,
"E").Value)) & _
.Cells(i, "E").Value
End If
Next i
End With

End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[maijiuli]

Thanks for reply GS,

I get an error here:

v = Application.WorksheetFunction.Rept("0", 6 - l) & v

Maybe I'm doing something wrong?

 

[Gary''s Student]

If the EE number exceeds 6, the code will die. Change:

If l = 6 Then

to

If l > 5 Then

 

[maijiuli]

Thanks Bob,

The code worked great. I know it's possible but not sure how? I would like
to plug this code into an existing macro so both can be ran at the same time.
Any suggestions would be great.

 

[Bob Phillips]

What does the existing macro look like and where would it fit in?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[maijiuli]

Nevermind,

I just called it using "Call".

Thank you very much,

 

[maijiuli]

Hi Bob,

Thanks again for your help. I have another question. Is it possible to
keep blanks blank instead of overiding them with 000000?

Thank you very much,

MJ

 

[Bob Phillips]

Public Sub ProcessData()
Dim i As Long, j As Long
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "E").End(xlUp).Row
For i = 2 To LastRow

If .Cells(i, "E").Text <> "" Then

If Len(.Cells(i, "E").Text) < 6 Then

.Cells(i, "E").Value = "'" & Left("00000", 6 - _
Len(.Cells(i, "E").Value)) & .Cells(i,
"E").Value
End If
End If
Next i
End With

End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)