我希望可以循环遍历我的数据透视表项并检查它们是否存在于另一个表中,参见我的示例截图:
因此,我想遍历所有颜色,检查它们是否存在于另一个表中(例如在另一个工作表中):
有没有办法这样做,以便出现一个消息框,指出紫色未在列表中找到?
非常感谢您的帮助!
![Example Pivot Table](https://istack.dev59.com/d6If5.webp)
![enter image description here](https://istack.dev59.com/l2wBf.webp)
非常感谢您的帮助!
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