vba - 如何在 Excel VBA 中使用输入框过滤数据透视表

Question

我想使用输入框过滤数据透视表。我需要使用户输入的值可见，其他值必须不可见。数据透视字段“数字”位于行标签中。我在代码中使用了循环。所以用户可以输入多个值。我使用了下面的代码，但问题是它不起作用并显示错误“下标超出范围”。帮我

Sub FilterRP()

Dim ws As Worksheet
Dim str1 As Variant
Dim arr1() As String
Dim i As Long

Set ws = Sheets("Main")

i = 1
Do
    str1 = Application.InputBox("Select one Number")
    ReDim Preserve arr1(i)
    arr1(i) = str1
    i = i + 1
Loop While (str1 <> vbNullString) And (str1 <> False)

ws.PivotTables("MainTable").PivotFields("Number").ClearAllFilters

ws.PivotTables("MainTable").PivotFields("Number").PivotItems(arr1(i)).Visible = True

End Sub

Answer 1

你可以尝试做这样的事情。它需要您的 InputBox 的输入被逗号隔开。然后循环遍历数据透视字段“数字”中的每个项目，如果该值存在于数组中，则设置可见性。如果数组 IsEmpty (即输入框是 vbNullString )然后它重置数据透视字段并返回所有项目。

Sub FilterRP()
    Dim ws As Worksheet
    Dim str1 As Variant
    Dim arr1() As String
    Dim pi As PivotItem

    Set ws = Sheets("Main")

    str1 = InputBox("Please enter the numbers you want to filter by" & vbNewLine & "Seperated by a comma (,)")
    ' Remove spaces if any
    str1 = Trim(Replace(str1, " ", vbNullString))

    arr1 = Split(str1, ",")
    With ws.PivotTables("MainTable").PivotFields("Number")
        For Each pi In .PivotItems
            If Not str1 = vbNullString Then
                pi.Visible = IIf(IsInArray(pi.Name, arr1), True, False)
            Else
                pi.Visible = True
            End If
        Next pi
    End With
End Sub

Public Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
    Dim i
    For i = LBound(arr) To UBound(arr)
        If arr(i) = stringToBeFound Then
            IsInArray = True
            Exit Function
        End If
    Next i
    IsInArray = False
End Function