On error VBA line activities where there is not an error

Question

I have a macro which runs through several pivot tables. In this one if the option isn't available e.g. I'm trying to select the number 50, but that isn't in the pivot table data. I want it to select blank instead. I've done an On error GoTo This works when the code is not there, but if the code IS there it acts like it isn't and returns blank values in the filter. Can anyone tell what I've done wrong here?

Sub SelectPromoter()

    Dim a As String
    Dim pt As PivotTable
    
    On Error GoTo Other
    
    ThisWorkbook.Worksheets("New Account Details - Name").Activate
    a = Worksheets("Selection").Cells(3, 1).Value
    
    For Each pt In ActiveSheet.PivotTables
        With pt.PivotFields("Promoter code")
            .ClearAllFilters
            .CurrentPage = a
        End With
        
Other:
        ActiveSheet.PivotTables("PivotTable4").PivotFields("Promotor code").CurrentPage = "(blank)"
        
        ' Select here to add any other key filters for the pivot table using fixed criteria
        
        ActiveSheet.PivotTables("PivotTable4").PivotFields("Milvus Account Name Change?").ClearAllFilters
        
        ActiveSheet.PivotTables("PivotTable4").PivotFields("Milvus Account Name Change?").CurrentPage = "(All)"
        With ActiveSheet.PivotTables("PivotTable4").PivotFields("Milvus Account Name Change?")
            .PivotItems("N").Visible = False
        End With
    
    Next

End Sub

`On Error...` needs a `Resume` statement or at least you need to reset the error or turn off error handling. After the line `Other:` try the line `On Error Goto 0` which is turning off error handling. This doesn't feel like a proper solution though. Be aware that someone may post an appropriate solution. — VBasic2008, Mar 30 '21 at 11:53

riskypenguin · Accepted Answer · 2021-03-30T12:41:52.693

If I understand your problem statement correctly, this will fix it:

Sub SelectPromoter()
    
    Dim wsAccountDetails As Worksheet
    Set wsAccountDetails = ThisWorkbook.Worksheets("New Account Details - Name")
    
    Dim a As String
    a = Worksheets("Selection").Cells(3, 1).Value
    
    On Error GoTo Data_NotFound
    Dim pt As PivotTable
    For Each pt In wsAccountDetails.PivotTables
        With pt.PivotFields("Promoter code")
            .ClearAllFilters
            .CurrentPage = a
        End With
        
        GoTo Data_Found
        
Data_NotFound:
        ActiveSheet.PivotTables("PivotTable4").PivotFields("Promotor code").CurrentPage = "(blank)"
Data_Found:
        
        With wsAccountDetails
            .PivotTables("PivotTable4").PivotFields("Milvus Account Name Change?").ClearAllFilters
            .PivotTables("PivotTable4").PivotFields("Milvus Account Name Change?").CurrentPage = "(All)"
            .PivotTables("PivotTable4").PivotFields("Milvus Account Name Change?").PivotItems("N").Visible = False
        End With
    Next pt

End Sub

The relevant line here is GoTo Data_Found. If you don't include that line, the code under the label Other: will be executed regardless of if an error occurs or not. That's why you need to "jump over it". GoTo-labels are not if-statements, which means they have no direct impact on control flow, you have to implement that logic manually by using GoTo. Although GoTo-statements aren't considered best practice, they do have some (if very few) genuine use cases. You can read more on how they work here.

I'm not used to working with pivot tables, but I assume there's a way to check if a certain value is available. Handling these situations properly is preferable to simply letting errors be thrown and catching them, as you won't notice different errors when they occur. If you decide to stick to this approach, you should at least constrain it as much as possible:

Sub SelectPromoter()
    
    Dim wsAccountDetails As Worksheet
    Set wsAccountDetails = ThisWorkbook.Worksheets("New Account Details - Name")
    
    Dim a As String
    a = Worksheets("Selection").Cells(3, 1).Value
    
    
    Dim pt As PivotTable
    For Each pt In wsAccountDetails.PivotTables
    
        On Error GoTo Data_NotFound
        With pt.PivotFields("Promoter code")
            .ClearAllFilters
            .CurrentPage = a
        End With
        On Error GoTo 0
        
        GoTo Data_Found
        
Data_NotFound:
        ActiveSheet.PivotTables("PivotTable4").PivotFields("Promotor code").CurrentPage = "(blank)"
Data_Found:
        
        With wsAccountDetails
            .PivotTables("PivotTable4").PivotFields("Milvus Account Name Change?").ClearAllFilters
            .PivotTables("PivotTable4").PivotFields("Milvus Account Name Change?").CurrentPage = "(All)"
            .PivotTables("PivotTable4").PivotFields("Milvus Account Name Change?").PivotItems("N").Visible = False
        End With
    Next

End Sub

Please take a look at where I moved the lines regarding the error handling On Error ....

Please also take a look at How to avoid using Select in Excel VBA. You should generally avoid using .Select and .Activate as it's inefficient and error-prone. I provided a better approach with the code, namely using a Worksheet object to address the sheet directly.

Thanks for this, the adjustment made sense. The actual code didn't fully work as when I changed the value in cell A3 the pivot table didn't pick this up but I used your "data not found/data found" logic on the original macro and that worked. I'm now also going through the .select and .activate changes. I'll post an update once its fully working. I will consider this as solved from your amendments. — FuriousD, Mar 30 '21 at 12:34
My bad, I forgot to replace `ActiveSheet` in the for-loop header, that might be what caused the error. — riskypenguin, Mar 30 '21 at 12:42
spot on - worked perfectly. Appreciate the help and education — FuriousD, Mar 30 '21 at 13:10

score 0 · Answer 2 · answered Mar 30 '21 at 12:27

0

If I understand your question correctly, problem is as your code is written, section below Other: will execute always and you want when no error occures the code should stop just before it right?

Solution is in this case simple - add before it Exit sub. So code below other will execute only when send by error handle

I am also VBA noob but generally Error handlers are written at the end of code and "normal" code is ended with Exit sub.

answered Mar 30 '21 at 12:27

Krikrinozka

46
3

I don't exiting the sub inside the for-loop is intended here, since the rest of the loop won't execute if you exit. – riskypenguin Mar 30 '21 at 12:30
I think you were on the right track with error handling at the end with an exit prior, but it's true that you don't want to break the `For` loop with `Exit Sub`. The solution is to take your approach but use `Exit For` instead of `Exit Sub`. – ed2 Mar 30 '21 at 12:35

On error VBA line activities where there is not an error

2 Answers2