Error handling pivot field VBA

[muhleebbin]

Hi!

I have the following code however on occasion I do run into an error when the pivot field (cat) that the code is looking for does not exist. Can someone help with the error handling code that would eliminate this?

Thank you in advance!

    Dim pt As PivotTable
    Dim field As PivotField
    Dim res As PivotField
    Dim cat As String

    Application.ScreenUpdating = False
    
    Set pt = Worksheets("MIR Pivot").PivotTables("PivotTable1")
    Set field = pt.PivotFields("PropertyName")
    Set res = pt.PivotFields("Resident")
    cat = Worksheets("MIR Pivot").Range("G2").Value

    With pt
        field.ClearAllFilters
        field.CurrentPage = cat
        pt.RefreshTable
    End With

 

[muhleebbin]

Nah that's my moronic butt not thinking of that too

Code works as expected and even tried other options as well.

 

[Domenic]

Okay that's great, glad you were able to sort it out.

Cheers!

 

[muhleebbin]

Sorry not exactly...

In this part of the code, the else half of it does not seem to work...I don't think I explained it very well in my initial assessment but the first half works in that it will select the cat when it is valid but if the selection is invalid (location that does not exist yet), it will still select all and not do the else "(blank)"

    With field
        .ClearAllFilters
       
        On Error Resume Next
        Set PvtItm = .PivotItems(cat)
        If Err.Number = 0 Then
            .CurrentPage = cat
        Else
            .CurrentPage = "(blank)"
        End If
        On Error GoTo 0

    End With

 

[Domenic]

When a selection is invalid, it should execute the else statement. If the pivot table doesn't update accordingly, it would suggest the pivot item doesn't exist. But I know that earlier in this thread you showed that it indeed exist. If you run that line of code in the Immediate Window, does that pivot table update? Or do you get an error? If you get an error, which error do you get?

 

[muhleebbin]

When a selection is invalid, it should execute the else statement. If the pivot table doesn't update accordingly, it would suggest the pivot item doesn't exist. But I know that earlier in this thread you showed that it indeed exist. If you run that line of code in the Immediate Window, does that pivot table update? Or do you get an error? If you get an error, which error do you get?

I get:

Compile error:

End With without With

 

[Domenic]

It's that one line of code that you should have entered in the Immediate Window, not all of the code. Just for clarity, enter the following line in the Immediate Window (Ctrl+G), and press ENTER. What do you get? Do you get an error? If so, which one?

Worksheets("MIR Pivot").PivotTables("PivotTable1").PivotFields("PropertyName").CurrentPage = "(blank)"

 

[muhleebbin]

Oops my fault....that line gives no errors and does what it is expected to do

 

[muhleebbin]

Just curious what is the PvtItm portion of the code supposed to do? All the other Dim's are used in some capacity in the rest of the code except this one.

 

[Domenic]

The PvtItm portion is there simply to check whether that pivot item exists. If it does, the CurrentPage is set to Cat. Otherwise, it's set to (blank).

Can you post the exact and complete code that you're using?

 

[muhleebbin]

ahhhh gotcha....thanks for explaining that to me

This is the complete code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
If Intersect(Target, Range("G12:I13")) Is Nothing Then Exit Sub
        
    Dim pt As PivotTable
    Dim field As PivotField
    Dim res As PivotField
    Dim PvtItm As PivotItem
    Dim cat As String
    Dim LR As Long
    Dim pt2 As PivotTable
    Dim field2 As PivotField
    Dim res2 As PivotField
    Dim PvtItm2 As PivotItem
    Dim cat2 As String
                
    Application.ScreenUpdating = False
    
    Set pt = Worksheets("MIR Pivot").PivotTables("PivotTable1")
    Set field = pt.PivotFields("PropertyName")
    Set res = pt.PivotFields("Resident")
    cat = Worksheets("MIR Pivot").Range("G2").Value

    With field
        .ClearAllFilters
        
        On Error Resume Next
        Set PvtItm = .PivotItems(cat)
        If Err.Number = 0 Then
            .CurrentPage = cat
        Else
            .CurrentPage = "(blank)"
        End If
        On Error GoTo 0

    End With
    
    pt.RefreshTable
    
    Set pt2 = Worksheets("GL R Pivot").PivotTables("PivotTable1")
    Set field2 = pt2.PivotFields("Property Name 2")
    Set res2 = pt2.PivotFields("Person/Description")
    cat2 = Worksheets("GL R Pivot").Range("G2").Value

    With field2
        .ClearAllFilters
        
        On Error Resume Next
        If Err.Number = 0 Then
            .CurrentPage = cat2
        Else
            .CurrentPage = ""
        End If
        On Error GoTo 0

    End With
    
    pt2.RefreshTable
    
    LR = Worksheets("MIR Pivot").Range("A" & Rows.Count).End(xlUp).Row - 4
        
    Sheets("CRC Report").Range("A18:I26").ClearContents
    Range("A17").Formula = "=IF($D17="""","""",XLOOKUP($D17,'MIR Mod'!$F$12:$F$500,'MIR Mod'!$A$12:$A$500,""""))"
    Range("B17").Formula = "=IF($D17="""","""",VLOOKUP($D17,'MIR Mod'!$F$12:$Z$500,19,FALSE))"
    Range("C17").Formula = "=IF($D17="""","""",XLOOKUP($D17,'MIR Mod'!$F$12:$F$500,'MIR Mod'!$D$12:$D$500,""""))"
    Range("D17").Formula = "=IF(ISBLANK('MIR Pivot'!A5),"""",'MIR Pivot'!A5)"
    Range("E17").Formula = "=IF($D17="""","""",VLOOKUP($D17,'MIR Mod'!$F$12:$Z$500,20,FALSE))"
    Range("F17").Formula = "=IF($D17="""","""",VLOOKUP($D17,'MIR Mod'!$F$12:$Z$500,21,FALSE))"
    Range("G17").Formula = "=IF($D17="""",0,VLOOKUP($D17,'MIR Mod'!$F$12:$Z$500,8,FALSE))"
    Range("H17").Formula = "=IF($D17="""",0,VLOOKUP($D17,'MIR Mod'!$F$12:$Z$500,9,FALSE))"
    Range("I17").Formula = "=IF($D17="""",0,VLOOKUP($D17,'MIR Mod'!$F$12:$Z$500,15,FALSE))"
    Range("V17").Formula = "=IF(ISNA(VLOOKUP($D17,'MIR Mod'!$F$12:$AA$500,22,FALSE)),"""",IF(VLOOKUP($D17,'MIR Mod'!$F$12:$AA$500,22,FALSE)=""x"",""Special"",""""))"
    Sheets("CRC Report").Range("A17:I17").Copy Range("A17:I" & LR + 16)
    Sheets("CRC Report").Range("V17").Copy Range("V17:V" & LR + 16)
    Range("D" & LR + 17).Formula = "=IF(ISBLANK('GL R Pivot'!A5),"""",'GL R Pivot'!A5)"
    Range("I" & LR + 17).Formula = "=IF(ISBLANK('GL R Pivot'!B5),,'GL R Pivot'!B5)"
                
End Sub