tiered error handling with handle

[Inactiver183192]

Errhandler:
On Error GoTo 0
On Error GoTo errhandler2:
 
    Do
 
Set lastfind = .Range("D1:D" & lastrow).Find(What:="", After:=.Range("D1"), LookIn:=xlValues, SearchDirection:=xlPrevious)
 
 
    If lastfind.Offset(0, 1).Value <> 0 And lastfind.Offset(0, 2).Value <> 0 Then

Hi all

I have a line that take my code to the first Errhandler line. However I want to reset so that the next error encountered will go to the errhandler2: line. Bascially I want to kind of reset the error handling so that any errors past the first few lines then links to the next handler that I have placed. LIke a tiered error handler. Is this possible? I'd appreciate any help! The code above doesn't work of course when it can't find blanks it brings up the error but I want it instead to move to the next handle thanks!

 

[AlphaFrog]

You can have multiple error handlers but only one is in effect at any given time. The key is to end a error handling block with a form of Resume. Do not use the Goto statement to direct code execution out of an error handling block.

I think this site explains it much better than I could.
Error Handling In VBA

On another note, you can test if the .Find method found a match without throwing an error.

<font face=Courier New>    <SPAN style="color:#00007F">Do</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> lastfind = <SPAN style="color:#00007F">Nothing</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> lastfind = .Range("D1:D" & lastrow).Find(What:="", After:=.Range("D1"), LookIn:=xlValues, SearchDirection:=xlPrevious)<br>        <br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> lastfind <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#007F00">'Match found</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> lastfind.Offset(0, 1).Value <> 0 And lastfind.Offset(0, 2).Value <> 0 <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#007F00">'''</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Else</SPAN><br>            <SPAN style="color:#007F00">' No match found</SPAN><br>            MsgBox "No empty cells found in D1:D" & lastrow, vbExclamation, "No Empty Cells"<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN></FONT>

 

[mikerickson]

One way to run different codes depening on how many times a error occures would be

Static ErrorSwitcher as Long

On Error Goto ErrorHandler
    rem code

Exit Sub

ErrorHandler:
    Select Case ErrorSwitcher
        Case 1
            Rem handle the error
            Err.Clear
            ErrorSwitcher = 2
            Resume
        Case 2
            Rem handle error differently
            Err.Clear
            ErrorSwitcher = 1
            Resume Next
    End Select
End Sub