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]

 

[NdNoviceHlp]

I'm guessing you need to use "on error resume next" and then If err.number <> 0 then err.clear (which should possibly be on error goto 0). Don't know why. Dave

 

[jai9]

Hi,

You need to replace "On Error GoTo String_error" with "On Error Resume Next".
Also delete "String_error:Err.Clear".
Then it should work as expected.

Jai

 

[vbaanalyst]

I'm guessing you need to use "on error resume next" and then If err.number <> 0 then err.clear (which should possibly be on error goto 0). Don't know why. Dave

Maybe I'm completely misunderstanding how error handling works (I'm pretty new to VBA) but my assumption would be that On error goto x would go to point x in the code then err.clear would clear any error codes? this appears to goto x on the first run, clear the error code - reset the error pointer but then 'not' go to x then next time?

 

[vbaanalyst]

Hi,

You need to replace "On Error GoTo String_error" with "On Error Resume Next".
Also delete "String_error:Err.Clear".
Then it should work as expected.

Jai

Why does on error GoTo 'not' work?
The actual error handling procedure I'd like to carry out is distinctly more complicated and involves manipulating the strings that are in the cells so having a GoTo would be preferable. A further problem with "resume next" is that it tags row containing only strings (see line 9 of example) as "remove me" which is not something I want to do.

I'm more interested in the mechanics of why on error GoTo doesn't work than having a working solution as this is the sort of problem I come across a lot and would like a solution to!

 

[Jonmo1]

Maybe I'm completely misunderstanding how error handling works (I'm pretty new to VBA) but my assumption would be that On error goto x would go to point x in the code then err.clear would clear any error codes? this appears to goto x on the first run, clear the error code - reset the error pointer but then 'not' go to x then next time?

You also have to do
On Error GoTo 0
After the Err.Clear
This resets Error Handling

Otherwise it thinks it's STILL handling the previous occurance of the error.


Try

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
    On Error GoTo 0
Next i
End Sub

 

[Norie]

What error(s) are you trying to handle?

 

[vbaanalyst]

I still get the same problem even with the on error goto 0
my code now looks like:

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
        
String_error:
    Err.Clear
    On Error GoTo 0
Next i


End Sub

In this case the errors I'm trying to handle are if a cell containing a string works its way into a cell used in a calculation (I know you can test for strings with IsNumeric) but more generally I'm trying to work out why using On Error GoTo rarely seems to work if it's within a loop.

 

[Norie]

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?

 

[Jonmo1]

Try this.

PS Have you considered using Application.WorksheetFunction with one of the worksheet count functions?[/QUOTE]
Yes, this has been considered
[QUOTE="vbaanalyst, post: 3387693, member: 236205"](I know you can test for strings with IsNumeric) but more generally I'm trying to work out why using On Error GoTo rarely seems to work if it's within a loop.[/QUOTE]

This is more an excersize trying to understand why it's not working.  Not so much needing to find a solution or workaround.


Unfortunately, I don't do alot of error handling.
I tend to evaluate the cause of the "reasonably expected" error, and then do tests for the situation that causes the error.

So I'm also a bit stumped here about why it's not working..It sure looks like it should...