使用VBA中的值过滤数据透视表

Im trying to filter a pivot table column (GP#) from an array (shortened in this example).

我试图从数组中过滤数据透视表列（GP＃）（在此示例中缩短）。

The filter does work but when I get to a number (in this case 83292) that is not in the filter, the excel crashes with the error :

过滤器确实有效，但当我得到一个不在过滤器中的数字（在这种情况下为83292）时，excel崩溃并出现错误：

Runtime error 1004, application-defined or object-defined error

运行时错误1004，应用程序定义或对象定义的错误

Is there a way of checking if a number/name etc is in the filter and if it is then apply to filter?

有没有办法检查过滤器中是否有数字/名称等，是否适用于过滤器？

My code:

我的代码：

vGP = Array("83041", "83327", "83292")
ActiveSheet.PivotTables("PivotTable1").ManualUpdate = True 
With ActiveSheet.PivotTables("PivotTable1").PivotFields("GP#")
    .PivotItems(1).Visible = True

    ' below code ensure pivot table filter does not cause error by not having anything in the filter

    For i = 2 To .PivotItems.Count
        .PivotItems(i).Visible = False
        If .PivotItems(i).Visible Then .PivotItems(i).Visible = False
    Next i

    ' goes through array and adds any value in array
    For Each i In vGP
        .PivotItems(i).Visible = True
    Next i
    On Error GoTo 0

Can anyone please help ensuring that the values in the array can be added to the filter and values in the array that are not present in the pivot table are ignored

任何人都可以帮助确保可以将数组中的值添加到过滤器中，并忽略数组中不存在于数据透视表中的值

1 个解决方案

#1

Try the code below to find if a certain array element is found within the PivotField you use named GP#.

尝试下面的代码，找出在您使用名为GP＃的PivotField中是否找到某个数组元素。

Dim PvtTbl As PivotTable
Dim PvtFld As PivotField
Dim MatchFound As Boolean, i As Long

' set the Pivot Table
Set PvtTbl = ActiveSheet.PivotTables("PivotTable1")

' set the Pivot Field
Set PvtFld = PvtTbl.PivotFields("GP#")

MatchFound = False ' reset flag
For i = 1 To PvtFld.PivotItems.Count ' loop through all pivot items
    If PvtFld.PivotItems(i).Name = vGP(1) Then ' check if the second array element is found in one of the Pivot items
        MatchFound = True ' raisw flag
        Exit For
    End If
Next i

If MatchFound Then
    PvtFld.PivotItems(i).Visible = True ' apply filter if the array element found
End If