Debugging Error in VBA Code -

i have following code adds zeroes number until number total of 7 digits long. worked fine until now, code is:

sub addzeroes() 'declarations dim integer, j integer, endrow long 'converts column format text format application.screenupdating = false columns("a:a").select selection.numberformat = "@" 'finds bottom row endrow = activesheet.range("a1").end(xldown).row 'selects top cell in column activesheet.range("a1").select  'loop move cell cell = 1 endrow - 1             'moves cell down 1. assumes there's header row starts @ row 2             activecell.offset(1, 0).select             'the do-while loop keeps adding zeroes front of cell value until     hits     length of 7 while len(activecell.value) < 7                             activecell.value = "0" & activecell.value             loop next application.screenupdating = true end sub 

the code loops through column , if number not have 7 total numbers adds 0s beginning. error appearing @ portion of code

for = 1 endrow - 1 

i cannot seem figure out wrong. portion tells macro once reaches end of list find blank space , move 1 stops on last number, , has worked until today.

you're using integer value i causing overflow in variable.

try this:

option explicit sub addzeroes() 'declarations dim cl range dim long, endrow long      application.screenupdating = false         'converts column format text format         columns("a:a").numberformat = "@"         'finds bottom row         endrow = activesheet.range("a1048576").end(xlup).row         '## or, excel 2003 , prior: ##'         'endrow = activesheet.range("a65536").end(xlup).row          'loop move cell cell         = 1 endrow - 1             set cl = range("a" & i)             cl             'the do-while loop keeps adding zeroes front of cell value until     hits     length of 7                 while len(.value) < 7                     .value = "0" & .value                 loop             end         next     application.screenupdating = true end sub