| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sorry, leading zeroes again :-(( | Excel Discussion (Misc queries) | |||
| Leading Zeroes | Excel Discussion (Misc queries) | |||
| Leading Zeroes | Excel Discussion (Misc queries) | |||
| CSV leading zeroes | Excel Discussion (Misc queries) | |||
| single quote 10-digit number that has leading zeroes & then conca. | Excel Worksheet Functions | |||
Hybrid Mode
