VBA筛选器错误 [英] VBA Filter error

问题描述

我有一个名为week_of_year(1,2,3,4,5,6,7,8 ....)的过滤字段，另一个枢轴字段年份(2012,2013,2014)，下面的vba代码对2014/2015的当前星期和年份，并隐藏其他所有内容.但是问题在于，对于大型数据透视表，代码将循环每个记录并运行缓慢.我正在尝试新代码，但遇到一些错误.

I have a filter field called week_of_year (1,2,3,4,5,6,7,8....), another pivot field year(2012,2013,2014), the vba code belows filter on the current week and year of 2014/2015 and hide everything else. But the problem is the code loops each record and runs slow when it comes to big pivot table. Im trying on a new code but encountering some error.

Sub datefilter()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("LO").PivotTables("PivotTable3")
Dim dd As Integer
dd = Format(Date, "ww")
Dim pf As PivotField
Dim pf1 As PivotField
Dim PI As PivotItem
Set pf =              

Worksheets("LO").PivotTables("PivotTable3").PivotFields("week_of_year")
Set pf1 =       
Worksheets("LO").PivotTables("PivotTable3").PivotFields("year")
PvtTbl.ClearAllFilters
For Each PI In pf.PivotItems
 If PI.Name = CStr(dd) Then

    PI.Visible = True
Else
    PI.Visible = False
End If
Next

For Each PI In pf1.PivotItems
 If PI.Name = "2014" Or PI.Name = "2015" Then
    PI.Visible = True
Else
    PI.Visible = False
End If
Next
End Sub

我开发的新代码是:

Sub datefilter1()
Dim PvtTbl As PivotTable
Set PvtTbl = Worksheets("LO").PivotTables("PivotTable3")
Dim dd As Integer
dd = Format(Date, "ww")
Dim pf As PivotField
Set pf =        
Worksheets("LO").PivotTables("PivotTable3").PivotFields("week_of_year")
pf.PivotFilters.Add2 xlValueEquals, 3
End Sub

代码在

pf.PivotFilters.Add2 xlValueEquals, CStr(dd)

我也尝试过:

pf.PivotFilters.Add2 Type:= xlValueEquals, Value1 := CStr(dd)

错误是无效的过程调用或参数.任何想法如何解决此错误?????

The error is Invalid procedure call or argument.Any idea How I can fix this error?????

有更快的方法可以对多个过滤条件进行过滤吗?谢谢！

Any faster way to filter on multiple filter criteria? Thanks!

推荐答案

如果只想根据一个值进行过滤，请将感兴趣的字段拖到报告过滤器"窗格中，则可以非常快速地执行此操作，而无需循环使用以下内容代码:

If you only want to filter on one value, drag the field of interest to the Report Filter pane, then you can do this very quickly without looping using the following code:

Sub FilterOnWeek()
Dim pt As PivotTable
Dim pf As PivotField


Set pt = Worksheets("LO").PivotTables("PivotTable3")
Set pf = pt.PivotFields("week_of_year")

pf.CurrentPage = Format(Date, "ww")
End Sub

如果由于某些原因该字段绝对必须作为行字段保留在数据透视表中，则如果您使用的是Excel 2010或更高版本，则仍然可以通过以下方法进行过滤而不会循环

If for some reason this field absolutely must stay in the PivotTable as a row field, you can still filter without looping by the following method if you have Excel 2010 or later

1. 复制数据透视表，然后拖动"week_of_year"字段 复制到报告过滤器"区域
2. 在一个数据透视表的week_of_year字段上设置切片器，然后 通过报表连接将其连接到另一个数据透视表 对话. (右键单击切片器"，选择报告连接")
3. 修改上面的代码，使其在第二个数据透视表上运行.

1. Make a copy of the PivotTable, and drag the 'week_of_year' field in the copy to the Report Filter area
2. Set up a slicer on the week_of_year field on one PivotTable, and connect it to the other PivotTable via the Report Connections dialog. (Right click on the Slicer, select 'Report Connections')
3. Amend the above code so it runs on the 2nd PivotTable.

切片器随后将同步两个数据透视表.

The Slicer will then sync the two PivotTables.

如果要使用个以上值进行过滤，请将字段拖到ROWS区域，并使用以下方法:

If you want to filter on more than one value, drag the field to the ROWS area, and use this:

Sub FilterOnWeek2()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim lStart As Long
Dim lEnd As Long


lEnd = Format(Date, "ww")
lStart = Val(Format(Date, "ww")) - 1

Set pt = Worksheets("LO").PivotTables("PivotTable3")
Set pf = pt.PivotFields("week_of_year")

With pf
    .ClearAllFilters
    .PivotFilters.Add Type:=xlCaptionIsBetween, Value1:=lStart, Value2:=lEnd
End With

End Sub

如果出于某些原因不希望将数据透视表中的该字段作为行字段，请按照与上述相同的一般方法:制作数据透视表的副本，将所需的字段放入可以在副本中作为ROWS字段进行筛选，将感兴趣的字段作为切片器连接在一起，将切片器隐藏在某处，然后修改代码，使其在具有行字段的数据透视表的副本上运行.

If for some reason you don't want this field in the PivotTable as a row field, follow the same general approach as above: make a copy of the PivotTable, put the field that you want to filter on in the copy as a ROWS field, connect the fields of interest together as a slicer, hide the slicer somewhere, and amend the code so it runs on the copy of the PivotTable with the rowfield in it.

我建议您阅读如果您想了解有关有效过滤数据透视表的更多信息，请http://dailydoseofexcel.com/archives/2013/11/14/filtering-pivots-based-on-external-ranges/.

这篇关于VBA筛选器错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！