VBA-测试值是否是PivotField的有效选择 [英] VBA - test if a value is a valid selection for a PivotField
问题描述
对于Sheet1上的数据透视表(pt1),我使用VBA通过以下代码更改过滤器字段(filterfield)的值.假设field的值可以是A,B或C
For a pivot table (pt1) on Sheet1, I use VBA to change the value of a filter field (filterfield) using the code below. Let's say values for field can be A, B or C
Sheets("Sheet1").PivotTables("pt1").PivotFields("filterfield").CurrentPage = "A"
有时,就此问题而言,假设为randomonly,A,B或C将不是filterfield的有效选择.当VBA尝试更改该字段时,它将引发运行时错误.我想避免这种情况.
Occasionally, and let's say randomonly for the purposes of this question, A, B or C will not be a valid selection for filterfield. When VBA attempts to change the field, it throws a run-time error. I want to avoid this.
在运行上面的代码之前,如何检查我的值对于filterfield是否有效?我想避免使用出错时",并且VBA不具有try/catch功能.
How can I check if my values are valid for filterfield before I run the code above? I would like to avoid using On Error and VBA does not have try/catch functionality..
推荐答案
您可以遍历PivotItems
并针对您的测试检查Name
.
You can iterate through the PivotItems
and check the Name
against your test.
Sub CheckIfPivotFieldContainsItem()
Dim pt As PivotTable
Set pt = Sheet1.PivotTables(1)
Dim test_val As Variant
test_val = "59"
Dim pivot_item As PivotItem
For Each pivot_item In pt.PivotFields("C").PivotItems
If pivot_item.Name = test_val Then
Debug.Print "MATCHES"
End If
Next pi
End Sub
相关数据表明匹配项应该存在,并且确实返回了MATCHES
.
Relevant data shows that a match should exist and indeed it returns MATCHES
.
这篇关于VBA-测试值是否是PivotField的有效选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!