Excel VBA遍历数据透视项 [英] Excel VBA loop through Pivot Items
本文介绍了Excel VBA遍历数据透视项的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我想遍历我的数据透视项并检查它们是否存在于另一个表中,请参见示例屏幕截图:
I want to loop through my pivot items and check if they exist in another table, see my Example Screenshot:
所以我想遍历所有颜色,检查它们是否存在于另一个表中(例如,在另一个工作表中):
So i want to loop through all the colors, checking if they exist in another table (e.g. in another sheet or so):
有没有办法做到这一点,所以会出现一个消息框,显示列表中没有找到紫色?
Is there any way to do this so there would appear a Message Box that the color purple was not found in the list?
非常感谢您的帮助!
推荐答案
您可以使用以下内容:
Sub ListMissingItems()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim rngList As Range
Dim strMsg As String
' change sheet and range
Set rngList = Worksheets("List").Range("A1:A10")
Set pt = ActiveSheet.PivotTables(1)
' change field as needed
Set pf = pt.PivotFields("Colour")
For Each pi In pf.PivotItems
If IsError(Application.Match(pi.Caption, rngList, 0)) Then strMsg = strMsg & vbLf & pi.Caption
Next pi
If Len(strMsg) > 0 Then MsgBox "The following items were not found in the list:" & strMsg
End Sub
这篇关于Excel VBA遍历数据透视项的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文