Excel VBA Code Error

Question

I have the following macro that adds 0s to ID numbers until they are 7 numbers long. I have used it countless times before and it has always worked without fail until today it started not working and the portion of the code For i = 1 To endrow - 1 is highlighted every time and I cannot debug the issue. The whole 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

what is the value of endrow at the point it stops? also, if the format is for display purposes only, then the whole routine can be replaced with Columns("A:A").NumberFormat = "0000000" — SeanC
– SeanC, Commented Jan 21, 2013 at 14:53
The endrow portion is to tell it stop on the very last row, normally it would go one extra row down and add a cell with just 7 0s. the -1 tells it stop on the very last cell. — user1958651
– user1958651, Commented Jan 21, 2013 at 15:03
when the code gets highlighted to indicate an error, what it the value of endrow at that point? — SeanC
– SeanC, Commented Jan 21, 2013 at 15:04
Does it error out on every ID number now? is the endrow different every time it errors out? what is an example error value? — Brad
– Brad, Commented Jan 21, 2013 at 15:25

Floris · Accepted Answer · 2013-01-21 21:27:27Z

3

Not sure what is causing the error - but would suggest another approach:

sub addZeros()
  Application.ScreenUpdating = False
  ' start at row 2 since OP said there's a header row

  Dim c as Range
  for each c in Range("A2", [A2].End(xlDown))
    c.Value = "'" & Format(c.Value, "00000000")
  next c
  Application.ScreenUpdating = True
end sub

A bit more compact...

Note that I'm adding the "'" apostrophe to make Excel treat the cell value as string. This is a safe way to make sure the zeros stay...

EDIT: Got rid of the last .Select to show it can be done, and is generally good practice as pointed out in comments.

edited Jan 21, 2013 at 21:27

answered Jan 21, 2013 at 17:28

Floris

46.6k7 gold badges73 silver badges128 bronze badges

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

2 Comments

RBarryYoung Over a year ago

+1: for getting rid of most of the .Select and Active* references

Floris Over a year ago

Thanks @RBarryYoung . I can get rid of the last select with for each c in Range("A2",[A2].End(xlDown)) . That would be even shorter and cleaner.

Collectives™ on Stack Overflow

Excel VBA Code Error

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