Error handling not working as expected

[vbaanalyst]

I have a simple bit of code for removing blank rows from a data set - it totals each row and tags it with “remove me” if the total is zero– but despite setting up error handling procedures I can’t stop it giving me an error when there are strings in the data and I want to know why!

Sub blank_tagger()

Set my_range = Range("A1:C10")
count_rows = my_range.Rows.Count
count_columns = my_range.Columns.Count

For i = 1 To count_rows
Total = 0
On Error GoTo String_error
For j = 1 To count_columns
Total = Total + Cells(i, j)
Next j
If Total = 0 Then
my_range.Cells(i, 1) = "remove me"
End If
String_error:
Err.Clear
Next i

End Sub

When one of the cells in my range is a text string then I would expect and error since you can’t add a string to a number and sure enough without error handling I receive is “error 13: type mismatch”. The problem is even ‘with’ error handling I receive this message!

I’m not looking for a workaround (using worksheetfunction.sum for example) as the code is fairly trivial – I just want to work out why the error handling doesn’t work!

Sample Data:
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64, align: right"]2[/TD]
[TD="width: 64, align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD="align: right"]8 [/TD]
[TD] string[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] string[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]

 

[vbaanalyst]

Try this.

For j = 1 To count_columns
            On Error Resume Next
            total = total + Cells(i, j)
            On Error Goto 0
        Next j

PS Have you considered using Application.WorksheetFunction with one of the worksheet count functions?

I'm trying to avoid using on error resume next as a) I'm trying to learn how to write error handling code and am missing something b)I want to write some functionality into the error handling and c) in this case it creates false labels for the "remove me" (e.g. i would not want row 9 of the sample data to have a "remove me" tag in column one as it contains data!)

I can successfully avoid the problem with "WorksheetFunction.sum(row_i)" or by using "if IsNumeric(cell) =true then" but I'm trying to work out WHY the problem exists - the "on error GoTo x" doesn't work as I would expect it to - when there is more than one error it doesn't GoTo!

 

[vbaanalyst]

Problem solved!

I found a dusty book in the corner of the office (it came with examples on a floppy disk...) that explained the problem. The error handling procedure is designed to a separate part of the code -> return you back to where the error occurred. so you're syntax should read

Sub error_handling_example()


For i = 1 To 10
    On Error GoTo handling_area
    'line of code with error


Next i


handling area:
    'fix error
    Resume (Next)
    


End Sub

if you use "resume" in the error handler it assumes the problem has been solved by your error handling code and tries to re-execute the offending line of code.
If you use "resume next" it assumes you've mitigated the error and resumes execution from the following line

the command "on error resume next" and "on error resume" does the same without going via an error handler

my fixed code is:

Sub blank_tagger()


Set my_range = Range("A1:C10")
count_rows = my_range.Rows.Count
count_columns = my_range.Columns.Count


For i = 1 To count_rows
    total = 0 'reset total
        
        For j = 1 To count_columns
            On Error GoTo String_error
            total = total + Cells(i, j)
        Next j
        
        If total = 0 Then
            my_range.Cells(i, 1) = "remove me"
        End If
        
Next i


Exit Sub


String_error:
    total = total +1 'or whatever code will fix your error
    Resume Next
End Sub

 

[Jonmo1]

Glad you got it sorted...

I could have sworn I tried that in my testing...lol

 

[jai9]

Hi,

Checked in a few sources and found that this might be the correct way of wring an error handler for loops.
Sub blank_tagger()

Set my_range = Range("A1:C10")
count_rows = my_range.Rows.Count
count_columns = my_range.Columns.Count

For i = 1 To count_rows
total = 0
On Error GoTo String_error
For j = 1 To count_columns
total = total + Cells(i, j)
Next j
If total = 0 Then
my_range.Cells(i, 1) = "remove me"
End If
Next i

String_error:
Err.Clear
On Error GoTo 0

End Sub

Jai

 

[xenou]

Sub blank_tagger()


Set my_range = Range("A1:C10")
count_rows = my_range.Rows.Count
count_columns = my_range.Columns.Count


For i = 1 To count_rows
    total = 0 'reset total
        
        For j = 1 To count_columns
            On Error GoTo String_error
            total = total + Cells(i, j)
        Next j
        
        If total = 0 Then
            my_range.Cells(i, 1) = "remove me"
        End If
        
Next i


Exit Sub

String_error:
total = total +1 'or whatever code will fix your error
Resume Next
End Sub

Another alternative:

Sub blank_tagger()


Set my_range = Range("A1:C10")
count_rows = my_range.Rows.Count
count_columns = my_range.Columns.Count


For i = 1 To count_rows
    total = 0 'reset total
        
        For j = 1 To count_columns
            If IsNumeric(Cell(i,j) Then
                total = total + Cells(i, j)
            End If
        Next j
        
        If total = 0 Then
            my_range.Cells(i, 1) = "remove me"
        End If
        
Next i

End Sub

This might be preferable in practice (the possible cause of the error is identified right inside the loop, so it's more self-descriptive what you are doing). Though in this case your goal seems to have been learning about error handling.

 

[NdNoviceHlp]

Richie(UK) offers a great tutorial here. HTH. Dave
http://www.mrexcel.com/forum/excel-questions/123526-problem-successive-error-statements.html
...oops Mark does as well

 

[HalfAce]

This works well for me...

Sub blank_tagger()

Set my_range = Range("A1:C10")
count_rows = my_range.Rows.Count
count_columns = my_range.Columns.Count

For i = 1 To count_rows
  Total = 0
  For j = 1 To count_columns
    On Error Resume Next
    Total = Total + Cells(i, j)
  Next j
  If Total = 0 Then
    my_range.Cells(i, 1) = "remove me"
  End If
String_error:
On Error GoTo 0
Next i

End Sub

Hope it helps.

 

[erik.van.geit]

Hi,
I do not have a solution, but after studying the case, came to this very small procedure to test.

Sub test()
    For i = 1 To 2
    Err.Raise 13
    Err.Clear
    Next i
End Sub

It's bugging on round 2 even when there is no doubt that the err was "cleared".

Also note that when replacing "on error goto 0" by "Resume Next" within the error handler (which is not the same as "on error resume next") all works fine.

kind regards,
Erik

 

[erik.van.geit]

Might have been a little short in my reply
This will not work and doesn't look cool to me because of the "On Error GoTo ErrorHandler" within the loop

Sub test()
    For i = 1 To 2
    On Error GoTo ErrorHandler
    Err.Raise 13
ErrorHandler:
        If Err Then
        MsgBox Err.Number
        Err.Clear
        On Error GoTo 0
        End If
    
    Next i
    
End Sub

This will work

Sub test()
On Error GoTo ErrorHandler
    For i = 1 To 2
    Err.Raise 13
ErrorHandler:
        If Err Then
        MsgBox Err.Number
        'Err.Clear
        Resume Next
        End If
    
    Next i
    
End Sub

So even without the "clear" line
best regards,
Erik

 

[ZVI]

1. I would use On Error Resume Next
Something like this:

Sub Test1()
 
  Dim a()
  Dim c As Long, cs As Long, r As Long, rs As Long
  Dim Total As Double
  Dim Rng As Range
 
  Set Rng = Range("A1:C10")
  a() = Rng.Value
  rs = UBound(a, 1)
  cs = UBound(a, 2)
 
  On Error Resume Next
 
  For r = 1 To rs
    Total = 0
    For c = 1 To cs
      Total = Total + a(r, c)
    Next
    If Total = 0 Then Rng.Cells(r, 1) = "remove me"
  Next
 
  Err.Clear
 
End Sub

2. For nesting of On Error GoTo ... the undocumented On Erorr GoTo -1 can be used. It disables 'in error handler' status and resets Err.Number to zero.

' How to use numerous 'On Error GoTo ...' in error handler
Sub Test2()
 
  Dim i As Integer
 
  On Error GoTo Err1
 
  i = 1 / 0         ' Raise "Division by zero" error
 
  Exit Sub
 
Err1:
 
  Debug.Print "Err1", Err.Number, Err.Description
 
  On Error GoTo -1    ' <-- the undocumented disabling of "in error handler" status
 
  Debug.Print "Goto -1", Err.Number, "Err is cleared, 'in error handler' status is disabled"
 
  On Error GoTo Err2  ' <-- this works because of previous 'On Error GoTo -1'
   i = 100000           ' Raise "Overflow" error
 
Err2:
 
  Debug.Print "Err2", Err.Number, Err.Description
 
End Sub