 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / March 2008macro 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,  SignatureThank You! 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 SignatureGary''s Student - gsnu200772 > Hello, > [quoted text clipped - 7 lines] > > I wish to have a macro so others can use too. Thanks for looking, Thanks for reply GS, I get an error here: v = Application.WorksheetFunction.Rept("0", 6 - l) & v Maybe I'm doing something wrong? SignatureThank You! > Sub fixum() > n = Cells(Rows.Count, "E").End(xlUp).Row [quoted text clipped - 21 lines] > > > > I wish to have a macro so others can use too. Thanks for looking, If the EE number exceeds 6, the code will die. Change: If l = 6 Then to If l > 5 Then SignatureGary''s Student - gsnu200772 > Thanks for reply GS, > [quoted text clipped - 29 lines] > > > > > > I wish to have a macro so others can use too. Thanks for looking, 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 Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hello, > [quoted text clipped - 11 lines] > > I wish to have a macro so others can use too. Thanks for looking, 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. SignatureThank You! > Public Sub ProcessData() > Dim i As Long, j As Long [quoted text clipped - 31 lines] > > > > I wish to have a macro so others can use too. Thanks for looking, What does the existing macro look like and where would it fit in? Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Thanks Bob, > [quoted text clipped - 45 lines] >> > >> > I wish to have a macro so others can use too. Thanks for looking, Nevermind, I just called it using "Call". Thank you very much, SignatureThank You! > What does the existing macro look like and where would it fit in? > [quoted text clipped - 47 lines] > >> > > >> > I wish to have a macro so others can use too. Thanks for looking, 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 SignatureThank You! > What does the existing macro look like and where would it fit in? > [quoted text clipped - 47 lines] > >> > > >> > I wish to have a macro so others can use too. Thanks for looking, 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 Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi Bob, > [quoted text clipped - 58 lines] >> >> > >> >> > I wish to have a macro so others can use too. Thanks for looking, |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.