VBA getting errors while using multiple On Error's in a macro

[Billy Hill]

I'm using multiple "ON ERROR" calls in a macro but I keep getting errors after the second iteration.

I'm using the Cells.Find function when I get the error. Here's some of the code and the error I get:

On Error GoTo NextOperation
Do While Cells.Find(What:="cont'd", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.EntireRow.Delete
ActiveCell.Offset(-1, 0).EntireRow.Delete
Loop
NextOperation:

On Error GoTo 0

Range("A1").Select
Selection.Value = "PN"
Range("B1").Value = "Temp"
Range("C1").Value = "TotQTY"

On Error GoTo NextOperation1
Do While Cells.Find(What:="Stock code", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.EntireRow.Delete
ActiveCell.Offset(-1, 0).EntireRow.Delete
ActiveCell.Offset(-2, 0).EntireRow.Delete
Loop
NextOperation1:

The second loop will go all the way through until the last line is found then error with "Run-time error 91: Object variable or With block variable not set" after it can't find any more instances. (I'm using the error generated to know that I'm at the end of the file and need to move on to the next find.)

I've tried to reset the error handling by using On Error GoTo 0 but still getting the error.

That part of the macro will run if I only run that part (I run to that point, stop the macro, then restart it and skip back to that line and start from there) so I'm sure the code works with only one On Error call.

Is there something else I need to do to reset the On Error handle besides On Error GoTo 0?

TYIA

 

[CalcSux78]

When On Error Goto 0 is in effect, it is the same as having no enabled error handler. Any error will cause VBA to display its standard error message box.

Check out this article on error handling... I've used it quite often. www.cpearson.com/excel/errorhandling.htm

 

[Billy Hill]

When On Error Goto 0 is in effect, it is the same as having no enabled error handler. Any error will cause VBA to display its standard error message box.

Check out this article on error handling... I've used it quite often. www.cpearson.com/excel/errorhandling.htm

Thanks CalcSux, that was a great read.

I was hoping that by using On Error GoTo 0 it would reset the Error Handler, then I could activate it again with a new handle, but for some reason it's not reading the new handle. I think it might be because I can only have one handler per procedure.

I didn't see it say that but I get that impression from this:

An error handling block, also called an error handler, is a section of code to which execution is tranferred via a On Error Goto <label>: statement. This code should be designed either to fix the problem and resume execution in the main code block or to terminate execution of the procedure. You can't use to the On Error Goto <label>: statement merely skip over lines. For example, the following code will not work properly:

On Error GoTo Err1:
Debug.Print 1 / 0
' more code
Err1:
On Error GoTo Err2:
Debug.Print 1 / 0
' more code
Err2:

When the first error is raised, execution transfers to the line following Err1:. The error hander is still active when the second error occurs, and therefore the second error is not trapped by the On Error statement.

I was able to get the macro to work but I had to split the 3 different search/delete functions into 3 separate macros then I just called one from the other. Not the cleanest way to do it I'm sure but it's running from start to finish now with no errors.

 

[Norie]

You could try handling the search term not being found yourself.

For example.

Dim rngFnd As Range

    Do
        Set rngFnd  = Cells.Find(What:="cont'd", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)
        If Not rngFnd Is Nothing Then
            rngFnd.Offset(-1, 0).EntireRow.Delete
            rngFnd.EntireRow.Delete
        End If
    Loop Until rngFnd Is Nothing

 

[Billy Hill]

You could try handling the search term not being found yourself.

For example.

Dim rngFnd As Range

    Do
        Set rngFnd  = Cells.Find(What:="cont'd", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)
        If Not rngFnd Is Nothing Then
            rngFnd.Offset(-1, 0).EntireRow.Delete
            rngFnd.EntireRow.Delete
        End If
    Loop Until rngFnd Is Nothing

That's interesting. If it doesn't find a match does it still throw an error? Imma hafta go play with that. Thanks!

 

[Norie]

When Find doesn't find a match it returns Nothing, which is what causes the error, so if we check for Nothing we can avoid the error.