Runtime error 1004 application-defined or object-defined error

Hello everyone! I am trying to run a macro that calculates some summary statistics on flooding data. Sometimes it works beautifully, and sometimes it returns a runtime error. The only pattern I can see is that it tends to happen when the area is >50% flooded, though not always. I've attached 1 working and 1 non-working data set. I have no idea why this error comes up. Any help would be much appreciated! Macro code is as follows (input row/column may need to be adjusted for each data set):


Sub WaterLevelMacro()
' Set RangeToIncrement = Selection
outputrow = 15
outputColumn = 12
Row = 13
Column = 6
Endrow = 2885
Do Until Row = Endrow
If Cells(Row, Column).Value > 0 Then
Do Until Cells(Row, Column).Value <= 0
Count = Count + 1
Sum = Sum + Cells(Row, Column).Value
Cells(outputrow, outputColumn).Formula = Count * 15
Cells(outputrow, outputColumn + 1).Formula = Sum / Count
Cells(outputrow, outputColumn + 2).Value = Cells(Row, 1).Value
Row = Row + 1
Loop
outputrow = outputrow + 1
Else

Row = Row + 1
Count = 0
Sum = 0
End If
Loop
Beep
End Sub

Hi tymkiw,

When you post code, you need 'code tags'. See the forum rules for instructions:
http://www.excelforum.com/forum-rule...rum-rules.html

Your error was caused by:

Please Login or Register  to view this content.

In the 'No Error' file all the rows around 2885 were negative so your error conditions were met.
In the 'Error' file the values were positive, so you went into an endless loop.

I modified the macro to read the value in Column 'F' as a string. If the value is EMPTY or BLANK the loop will be exited. I had to save the previous value in each loop to determine when to increment the output row.


Try the following code (tested and working in both files).

Please Login or Register  to view this content.

Here are a couple of tips:
To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. dim i as Integer). http://www.cpearson.com/excel/DeclaringVariables.aspx

Debugger Secrets:
a. Press 'F8' to single step (goes into subroutines and functions).
b. Press SHIFT 'F8' to single step OVER subroutines and functions.
c. Press CTRL 'F8' to stop at the line where the cursor is.
d. 'Left Click' the margin to the left of a line to set (or clear) a BREAKPOINT.
e. Press CTRL 'G' to open the IMMEDIATE WINDOW. 'debug.print' statements send their
output to the IMMEDIATE WINDOW.
f. Select View > Locals to see all variables while debugging.
g. To automatically set a BREAKPOINT at a certain location put in the line:
'Debug.Assert False'
h. To conditionally set a BREAKPOINT at a certain location put in lines similar to:
if i >= 20 and xTV20 > 99.56 then
Debug.Assert False
endif
i. A variable value will be displayed by putting the cursor over the variable name.

Lewis

Thank you so much! That seems to have done it.

Liz