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)



"maijiuli" wrote in message
...
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
--
Thank You!


"Bob Phillips" wrote:

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" wrote in message
...
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.


--
Thank You!


"Bob Phillips" wrote:

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" wrote in message
...
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,

--
Thank You!