| |
You are here: Experts > Computing/Technology > Microsoft Software > Excel > Error with looping macros
Excel - Error with looping macros
Expert: Aidan Heritage - 11/7/2009
Question
In Microsoft Excel 2003
I have a macro set up that pulls information from a database where the reference numbers start on Row 25 and then have 12 additional rows of information. Then it moves on to the next reference number. The macro is set up like this:
Row2 = 25
Do Until Cells(Row2, 1).Value = 0
If Cells(Row2, 1).Value = ABC Then
If Cells(Row2 + 3, 4).Value > 0 Then
AvgTime = Cells(Row2 + 7, 4).Value
Else
AvgTime = 0
End If
End If
Row2 = Row2 + 12
Loop
This works perfectly fine until it gets to row 32761. If there is anything written in (Row 32761, Column 1), an error message pops up that says "Run-time error '6': Overflow."
I deleted the information in that row, and the macro resumed working properly. However, because there is no reference number in that cell, it doesn't track information for any of the reference numbers listed after that one.
I tried shifting everything down one cell and changing the macro from "Row2 = 25" into "Row2 = 26" but the same error occurred. So I know it's not an issue with the individual cell (Row 32761, Column 1). It seems to be an issue with the looping of "Row2 = Row2 + 12"
Is there a limit to looping macros? Does it max out once it gets to a certain value? Row 32761 is pretty high up there... How can I continue to track data past that row? Any help at all would be greatly appreciated. Thanks.
Answer
There isn't a limit for looping as such, but there IS a limit for variables - you haven't included any DIM statements but possibly one of the variables is declared as an integer which has a maximum size of 32768 which is a 15bit binary number (2 to the power 15) - it would seem to be THIS that is the limit - and therefore I would IMAGINE that it's the row2 variable that is at fault - try
dim Row2 as double
and then run it.
Add to this Answer Ask a Question
|
|
