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:

Endrow = 2885
Do Until Row = Endrow

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).

Option Explicit

Sub WaterLevelMacro()
' Set RangeToIncrement = Selection

  Dim iOutPutColumn As Long
  Dim iOutputRow As Long
  Dim iColumn As Long
  Dim iCount As Long
  Dim iRow As Long
  
  Dim bNeedMore As Boolean
  
  Dim xSum As Double
  Dim xValue As Double
  Dim xValuePrevious As Double
  
  Dim sValue As String

  iOutputRow = 15
  iOutPutColumn = 12
  
  iRow = 13

  bNeedMore = True
  While bNeedMore = True
  
    'Read the next value as a string
    'Terminate when the value is BLANK or EMPTY
    sValue = Trim(Cells(iRow, "F").Text)
    If Len(sValue) > 0 Then
      xValue = CDbl(sValue)
    Else
      bNeedMore = False
      xValue = -1#
    End If
    
    
    If xValue > 0 Then
      iCount = iCount + 1
      xSum = xSum + xValue
      Cells(iOutputRow, iOutPutColumn).Value = iCount * 15
      Cells(iOutputRow, iOutPutColumn + 1).Value = xSum / iCount
      Cells(iOutputRow, iOutPutColumn + 2).Value = Cells(iRow, 1).Value
    ElseIf xValuePrevious > 0 Then
      iOutputRow = iOutputRow + 1
      iCount = 0
      xSum = 0
    End If
    
    'Prepare for the next loop
    'Save the old value to indicate when to increment to the next output row
    xValuePrevious = xValue
    iRow = iRow + 1
  
  Wend
  
  Beep
  
End Sub

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