On Error GoTo (Not Working)

[kentster]

Hi all,

My code is below. The first part works like a charm and when it errors out it goes to the OnPremise section. However, in the OnPremise section it errors out but does NOT go to "Exit_Here". What am I missing?

 HigherEd:
       On Error GoTo OnPremise:
    
    ActiveSheet.Range("$A$5:$BW$886").AutoFilter Field:=16, Criteria1:="X", Operator:=xlOr, Criteria2:="B"
    ActiveSheet.Range("$A$5:$BW$886").AutoFilter Field:=9, Criteria1:=Array( _
        "Forecast", "Pipeline", "Upside"), Operator:=xlFilterValues
    Range("BX5").Select
    Application.CutCopyMode = False
    Rows("5:5").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Selection.AutoFilter
    Range("BX1").Select
    ActiveSheet.Range("$A$5:$BY$886").AutoFilter Field:=76, Criteria1:="1"
    ActiveSheet.Range("$A$5:$BY$886").AutoFilter Field:=2, Criteria1:= _
        "Higher Ed"
    ActiveSheet.Range("$A$5:$BY$886").AutoFilter Field:=5, Criteria1:= _
        "PIYUSH.KAPOOR@ORACLE.COM"
    Range("BW1").Select
    Selection.Copy
    Range("BW6:BW922").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
        ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("5:5").Select
    ActiveSheet.ShowAllData
    
    'For OnPremise"
    
OnPremise:
      On Error GoTo Exit_Here:
       
    ActiveSheet.Range("$A$5:$BW$886").AutoFilter Field:=16, Criteria1:="X", Operator:=xlOr, Criteria2:="B"
    ActiveSheet.Range("$A$5:$BW$886").AutoFilter Field:=9, Criteria1:=Array( _
        "Forecast", "Pipeline", "Upside"), Operator:=xlFilterValues
    Range("BX5").Select
    Application.CutCopyMode = False
    Rows("5:5").Select
    Application.CutCopyMode = False
    Selection.AutoFilter
    Selection.AutoFilter
    Range("BX1").Select
    ActiveSheet.Range("$A$5:$BY$886").AutoFilter Field:=76, Criteria1:="1"
    ActiveSheet.Range("$A$5:$BY$886").AutoFilter Field:=2, Criteria1:= _
        "OnPremise"
    ActiveSheet.Range("$A$5:$BY$886").AutoFilter Field:=5, Criteria1:= _
        "JOANIE.BALACH@ORACLE.COM"
    Range("BW1").Select
    Selection.Copy
    Range("BW6:BW922").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Rows("5:5").Select
    ActiveSheet.ShowAllData
    Range("BW1").Select
    Selection.ClearContents
    
   
    'Message that macro is done


Exit_Here:
    Rows("5:5").Select
    ActiveSheet.ShowAllData
    Range("BW1").Select
    Selection.ClearContents
    Range("A1").Select
    


    MsgBox "Macro Completed Successfully"


   End Sub

[end code]

Thanks for the assist
Kent

 

[Norie]

What error(s) are you trying to handle?

 

[kentster]

Run time 1004

 

[Eric W]

The main issue is that you can't have an error handler within an error handler. Typically, you'd first select the error handler with the On Error command, then within that handler you execute some lines of code that allow you to continue, then you Resume back to the main program. (Or just print an error message and quit.) Resume, Resume Next, or Resume SomeLine:

If you don't use Resume, then VBA assumes you're still in an error handler. So given your code, you could do something like:

Sub test1()


Initialization:
    On Error GoTo Handler1:
    
    ' some code
    
NextSection:
    On Error GoTo Handler2:
    
    ' some code
    
Finalization:


    ' some code
    
    Exit Sub
    
Handler1:
    ' do something
    Resume NextSection:
    
Handler2:
    'do something
    Resume Finalization:
    
End Sub

However, I wouldn't really consider that to be a great way to do it. Your 2 sections look nearly identical, except for 1 criterion and 1 email address. You could put the two sections in a loop and clean things up a lot.

Good luck!

 

[kentster]

Thanks, Eric. It took me some time to get back to this as i was pulled another direction. That said, and for my own sake of sanity, I am going to work through your suggestion and use the "dirty" way. I am totally new to VBA and learn what i can just by reading and seeing examples such as yours. The cleaner would be great to learn but I simply do not want you or anyone else to provide it and me to just copy it and not learn from it.

Thanks again.