macro to add leading zeroes to number and loop
 

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!

macro to add leading zeroes to number and loop
 

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

--
Gary''s Student - gsnu200772


"maijiuli" wrote:

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!

macro to add leading zeroes to number and loop
 

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!

macro to add leading zeroes to number and loop
 

Thanks for reply GS,

I get an error he

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

Maybe I'm doing something wrong?

--
Thank You!


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200772


"maijiuli" wrote:

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!

macro to add leading zeroes to number and loop
 

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

If l = 6 Then

to

If l 5 Then

--
Gary''s Student - gsnu200772


"maijiuli" wrote:

Thanks for reply GS,

I get an error he

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

Maybe I'm doing something wrong?

--
Thank You!


"Gary''s Student" wrote:

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

--
Gary''s Student - gsnu200772


"maijiuli" wrote:

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!

macro to add leading zeroes to number and loop
 

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!

macro to add leading zeroes to number and loop
 

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!

macro to add leading zeroes to number and loop
 

Nevermind,

I just called it using "Call".

Thank you very much,
--
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!

macro to add leading zeroes to number and loop
 

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!

macro to add leading zeroes to number and loop
 

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!