Excel VBA loop through Pivot Items

问题:

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?

Many thanks for your help!

回答1:

You can use something like this:

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