On Error GoTo within On Error GoTo

[Walker5]

My second error handler doesn't work. I am getting a Run-time error from the line after "On Error GoTo Error_Handler_2" in this macro:

Sub Macro1()
If Sheets("Sheet5").PivotTables("PivotTable2").PivotFields("Type").PivotItems("a").Position = 1 Then x = "a"
On Error GoTo Error_Handler
If Sheets("Sheet5").PivotTables("PivotTable2").PivotFields("Type").PivotItems("e").Position = 2 Then x = "e"
Exit Sub
Error_Handler:
On Error GoTo Error_Handler_2
If Sheets("Sheet5").PivotTables("PivotTable2").PivotFields("Type").PivotItems("f").Position = 1 Then x = "f"

Error_Handler_2:
MsgBox "Unsolvable Error"
End Sub

I thought that a second "On Eror GoTo" would work within the first "On Eror GoTo" This is for a Pivot table that only has fields "a", "b" and "c", no "e" or "f" fields. Any thoughts would be greatly appreciated.

 

[JoeMo]

Try this (untested):

Error_Handler:
On Error GoTo 0
On Error GoTo Error_Handler_2
'rest of code

 

[Walker5]

No such luck. Any other ideas?

 

[JoeMo]

Since the statement producing the error is immediately before your second error handler try this:

Error_Handler:
On Error GoTo 0
On Error Resume Next
If Sheets("Sheet5").PivotTables("PivotTable2").PivotFields("Type").PivotItems("f").Position = 1 Then x = "f"
MsgBox "Unsolvable Error"
End Sub

 

[Walker5]

Nope, still getting the same error. It's as if VBA doesn't recognize "On Error" because it is already within an "On Error" statement.

 

[Gary McMaster]

Just a guess:

Try "Resume" as your last statement in your first error handler.

Gary

 

[Norie]

What error are you trying to catch?

 

[Andrew Poulsom]

Try:

Sub Macro1()
    If Sheets("Sheet5").PivotTables("PivotTable2").PivotFields("Type").PivotItems("a").Position = 1 Then x = "a"
    On Error GoTo Error_Handler
    If Sheets("Sheet5").PivotTables("PivotTable2").PivotFields("Type").PivotItems("e").Position = 2 Then x = "e"
    Exit Sub
Error_Handler:
    Resume Here
Here:
    On Error GoTo Error_Handler_2
    If Sheets("Sheet5").PivotTables("PivotTable2").PivotFields("Type").PivotItems("f").Position = 1 Then x = "f"
    Exit Sub
Error_Handler_2:
    MsgBox "Unsolvable Error"
End Sub

 

[Walker5]

Andrew,
It worked like a charm. You're the man and quite deserved of the tilte "MrExcel MVP." Thank you. I will file that in my "Excel things to remember" workbook along with about a million other things.

Thanks again!