Debugging Error in VBA Code

Question

I have the following code that adds zeroes to a number until the number is a total of 7 digits long. It was worked fine up until now, the code is:

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

The code loops through the A column and if a number does not have 7 total numbers it adds 0s to the beginning. THe error is appearing at the portion of code

FOR I = 1 TO ENDROW - 1

I cannot seem to figure out what is wrong. This portion tells the macro that once it reaches the end of the list to find the blank space and move up 1 so it stops on the last number, and it has worked up until today.

so what is the error ?

Satya
– Satya

2013-05-08 17:50:15 +00:00
Commented May 8, 2013 at 17:50 — Satya
– Satya, Commented May 8, 2013 at 17:50
I added that in the end

user2119980
– user2119980

2013-05-08 17:52:21 +00:00
Commented May 8, 2013 at 17:52 — user2119980
– user2119980, Commented May 8, 2013 at 17:52
What is the error? Do you get a specific error message?

David Zemens
– David Zemens

2013-05-08 17:57:18 +00:00
Commented May 8, 2013 at 17:57 — David Zemens
– David Zemens, Commented May 8, 2013 at 17:57
what is the value of i when this error happens?

David Zemens
– David Zemens

2013-05-08 18:00:39 +00:00
Commented May 8, 2013 at 18:00 — David Zemens
– David Zemens, Commented May 8, 2013 at 18:00

David Zemens · Accepted Answer · 2013-05-08 18:03:58Z

1

You're using an integer value for i which is likely causing an overflow in that variable.

Try this:

Option Explicit
Sub AddZeroes()
'Declarations
Dim cl As Range
Dim i As Long, endrow As Long

    Application.ScreenUpdating = False
        'Converts the A column format to Text format
        Columns("A:A").NumberFormat = "@"
        'finds the bottom most row
        endrow = ActiveSheet.Range("A1048576").End(xlUp).Row
        '## Or, for Excel 2003 and prior: ##'
        'endrow = ActiveSheet.Range("A65536").End(xlUp).Row

        'loop to move from cell to cell
        For i = 1 To endrow - 1
            Set cl = Range("A" & i)
            With cl
            'The Do-While loop keeps adding zeroes to the front of the cell value until it     hits     a length of 7
                Do While Len(.Value) < 7
                    .Value = "0" & .Value
                Loop
            End With
        Next i
    Application.ScreenUpdating = True
End Sub

answered May 8, 2013 at 18:03

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2119980 Over a year ago

That is exactly what was causing it, thanks so much for your help.

David Zemens Over a year ago

Glad it worked! Note some revisions to the rest of your code, too. Try to avoid things like Select and ActiveCell whenever possible (99.9% of the time). :) Usually these constructs are not at all necessary.

Collectives™ on Stack Overflow

Debugging Error in VBA Code

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related