#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    4
    Rep 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
  2. #2
  3. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,954
    Rep Power
    8617
    Why not just do something like ...

    Code:
    Activesheet.Columns("A:A").Numberformat="0000000"
    ?

    But with your solution you might want to change

    Code:
    endrow = ActiveSheet.Range("A1").End(xlDown).Row
    to

    Code:
    endrow = Activesheet.cells.specialcells(xlcelltypelastcell).row
    Last edited by medialint; February 5th, 2013 at 02:54 PM.
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2013
    Posts
    18
    Rep Power
    0

    use excel


    easiest way is in excel if you are using VBA

    put your number in a cell say A1 and then put

    = text(a1,"0000000") in another cell

IMN logo majestic logo threadwatch logo seochat tools logo