On Error GoTo (Not Working)

kentster

New Member
Joined
Nov 22, 2016
Messages
28
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?

Code:
 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
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,219
Office Version
  1. 365
Platform
  1. Windows
What error(s) are you trying to handle?
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,639
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:

Code:
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

New Member
Joined
Nov 22, 2016
Messages
28
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,843
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top