February 5th, 2013, 02:18 PM
|
|
Registered User
|
|
Join Date: Feb 2013
Posts: 4
Time spent in forums: 1 h 14 m 37 sec
Reputation Power: 0
|
|
|
Add Zeroes Macro Stopped Working
In my job I deal with employee Id's alot and we have variations of them and sometimes I need to convert them to the other form which is as simple as adding enough zeroes to the beginning of the number until it is 7 digits long. It is not simple when I have hundered or thousands of IDs to convert so i made a macro which goes through the list of IDs and adds the correct number of zeroes until the id has 7 total digits. And it has worked every time I used it up until now and I cannot figure out what is wrong with it. Here is the code, the error is in the "For i = 1 To endrow -1" Like I said, this code has worked every time I have used it up until now. And if it does work it only adds the zeroes to the first cell, but it stops after that which it is not supposed to.
The way it works is, the numbers must be in the A column of the spreadsheet and it checks to see how long the number is then adds the approrpriate number of zeroes if it needs them, then loops to the next. it continues to loop until it reaches a blank cell.
Code:
Sub AddZeroes()
'Declarations
Dim i As Integer, j As Integer, endrow As Long
'Converts the A column format to Text format
Application.ScreenUpdating = False
Columns("A:A").Select
Selection.NumberFormat = "@"
'finds the bottom most row
endrow = ActiveSheet.Range("A1").End(xlDown).Row
'selects the top cell in column A
ActiveSheet.Range("A1").Select
'loop to move from cell to cell
For i = 1 To endrow - 1
'Moves the cell down 1. Assumes there's a header row so really starts at row 2
ActiveCell.Offset(1, 0).Select
'The Do-While loop keeps adding zeroes to the front of the cell value until it hits a length of 7
Do While Len(ActiveCell.Value) < 7
ActiveCell.Value = "0" & ActiveCell.Value
Loop
Next i
Application.ScreenUpdating = True
End Sub
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
