时间戳

首页 > 解决方案 > 数据透视表过滤器未更改为循环中的每个值

excel - 数据透视表过滤器未更改为循环中的每个值

问题描述

我看过很多类似的帖子，但没有一个能够解决我更改数据透视表过滤字段的问题。我试图通过一个循环进行过滤，该循环获取 ws2 列表中的每个值并将更改粘贴FilterID到该值。但是，在我尝试过的所有不同方法中，将其设置为一个值，将其设置为一个字符串，使用"CurrentPage"，它们都没有工作或导致 1004 错误。我最近的努力如下。如何"MatchFilter"根据下面循环中的值来更改？

下面的方法只是给出一个 1004 Application or object not defined 错误。

我也尝试添加MatchFilter.Orientation = xlPageField但没有成功。

Sub Filter_Test()

Dim ws1 As Worksheet
Set ws1 = Sheets("Order_Groupings")

'The match list is the list of unique Match IDs to filter through, pasting each in the filter
Dim ws2 As Worksheet
Set ws2 = Sheets("Match_List")

'Here we will paste the results from each optimizer run
Dim ws3 As Worksheet
Set ws3 = Sheets("Optimizer_Results")

Dim pt As PivotTable
Set pt = ws1.PivotTables("Order_Groupings")

Dim FilterID As String

Dim MatchFilter As PivotField
Set MatchFilter = pt.PivotFields("Match_ID")

Dim numIDs As Integer

'This is the number of different match IDs
'Match count is set to J4 right now in the Match List tab
numIDs = ws2.Range("match_count").Value

'For loop to cycle through each Match ID
For i = 1 To numIDs
    
    FilterID = ws2.Range("A4").Offset(i, 0).Value
    
    'Trying to set the MatchFilter to the new value in FilterID
    With MatchFilter
        .ClearAllFilters
        .CurrentPageName = FilterID
    End With

Next i
    
End Sub

标签： excelvbapivot-table

解决方案

这最终通过事先清除所有过滤器并使用 PivotFilter.Add Type:=xlCpationEquals

Sub Filter_Test()

Dim ws1 As Worksheet
Set ws1 = Sheets("Order_Groupings")

'The match list is the list of unique Match IDs to filter through, pasting each in the filter
Dim ws2 As Worksheet
Set ws2 = Sheets("Match_List")

'Here we will paste the results from each optimizer run
Dim ws3 As Worksheet
Set ws3 = Sheets("Optimizer_Results")

Dim pt As PivotTable
Set pt = ws1.PivotTables("Order_Groupings")

Dim FilterID As String

Dim MatchFilter As PivotField
Set MatchFilter = pt.PivotFields("Match_IDs")

Dim numIDs As Integer

Dim i As Integer

'This is the number of different match IDs
'Match count is set to J4 right now in the Match List tab
numIDs = ws2.Range("match_count").Value

'For loop to cycle through each Match ID
For i = 1 To numIDs
    
    FilterID = ws2.Range("A4").Offset(i, 0)
    
    MatchFilter.ClearAllFilters

    MatchFilter.PivotFilters.Add Type:=xlCaptionEquals, Value1:=FilterID

Next i
    
End Sub

推荐阅读