循环通过报告过滤器来改变可见性不起作用

本文介绍了循环通过报告过滤器来改变可见性不起作用的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在选择一个报告过滤器，在这种情况下为加拿大。这意味着其余的必须被隐形。 此代码无问题：

I'm trying to select one report filter, in this case Canada. That means the rest must be made invisible. This code works without issue:

Public Sub FilterPivotTable() With ActiveSheet.PivotTables("Epidemiology").PivotFields("COUNTRY") .PivotItems("Canada").Visible = True .PivotItems("USA").Visible = False .PivotItems("Germany").Visible = False .PivotItems("France").Visible = False End With End Sub

但是，当我们将其他国家添加到我们的流行病学数据透视表中时，我正在努力准备，所以我试图有一个for循环。此代码不起作用：

However, I'm trying to prepare for when we add other countries to our "Epidemiology" pivot table, so I tried to have a for loop. This code doesn't work:

With ActiveSheet.PivotTables("Epidemiology").PivotFields("COUNTRY") .PivotItems("Canada").Visible = True For Each Pi In .PivotItems If Pi.Value = "CANADA" Then Pi.Visible = True Else Pi.Visible = False End If Next Pi End With

它给了我一个错误，在$ code> Pi.Visible = False 行。我得到的错误是运行时错误'1004'：无法设置PivotItem类的可见属性

It gives me an error on the Pi.Visible = False line. The error that I get is Run-time error '1004': Unable to set the Visible property of the PivotItem class

为什么它不能在for循环中运行？

Why doesn't it work inside a for loop?!

令人沮丧的是，在线查找的所有示例都使用类似的语法。 （有些使用索引，但是我尝试了并且得到相同的错误。）

Frustratingly, all the examples I find online use similar syntax. (Some use an index, but I tried that and got the same error.)

推荐答案

在一个可枢纽的过滤器中，您必须具有在任何时候至少选择一个项目。即使您打算在代码中稍后选择一个。

In a pivottable filter, you must have at least one item selected at all times. Even if you intend to select one later in the code.

With Pt.PivotFields("COUNTRYSCENARIO") ' Sets all filters to true, resetting it. .ClearAllFilters ' This is necessary if you want to select any options ' other than "All Pivot Items = Visible" and ' "OnlyOneSpecificPivotItem = Visible" .EnableMultiplePageItems = True If .PivotItems.Count > 0 Then ' goofy but necessary Set firstPi = .PivotItems(1) For Each Pi In .PivotItems ' Make sure that that first pivot item is visible. ' It gets mad if it's already visible and you ' set it to visible with firstPi.Visible = True ' ...pretty silly If firstPi.Visible = False Then firstPi.Visible = True ' Don't loop through firstPi If Pi.Value <> firstPi.Value Then If Pi.Value = opt1 Or Pi.Value = opt2 Or Pi.Value = opt3 Then Pi.Visible = True ElseIf Pi.Visible = True Then Pi.Visible = False End If End If Next Pi ' Finally perform the check on the first pivot item If firstPi = opt1 Or firstPi = opt2 Or firstPi = opt3 Then firstPi.Visible = True Else firstPi.Visible = False End If End If End With

请注意，如果您尝试不选择任何内容，例如opt 1 =，而opt2 =和opt3 =，您将会出现相同的错误：您必须至少选择一个枢纽项。

Notice that if you try to select nothing, e.g. opt 1 = "" and opt2 = "" and opt3 = "", you will have that same error: you must have at least one pivot item selected.