CODEWITHSUNDEEP
excelvbaloopspivotpivot-table
Spurious
Spurious

Reputation: 1995

Find the address of a specific PivotItem

I have the value for RowField.Name and the value for PivotItem.Name in RowFields, I want to highlight the specific cell. But I can't seem to find a way that allows me to loop through every visible RowField.

Ideally I want something like this:

Sub LoopThroughPivotAndFindValue()
dim pt as PivotTable, pi as PivotItem, pf as PivotField

Set pt = wb.PivotTables("PivotTableNo1")

For each pf in pt.PivotFields
  For each pi in pf.PivotItems
    If pf.Name = "Test" And pi.Name = "Value" Then
      'Ideally here would be the output of the address within the sheet
    End If
  Next pi
next pf
End Sub

Ultimately I want to color specific cells in the pivot table but I cannot seem to find a way to do it properly. There is no index property that I am aware of.

Anyone with an idea?

Upvotes: 1

Views: 2371

Answers (2)

R3uK
R3uK

Reputation: 14537

An UDF can be useful too, this might be a good start for you :

Sub test_Spurious()
    MsgBox LoopThroughPivotAndFindValue("Test", "Value")
End Sub

And here is the function :

Function LoopThroughPivotAndFindValue(ByVal PivotFieldName As String, ByVal PivotItemName As String) As String
Dim pT As PivotTable, _
    pI As PivotItem, _
    pF As PivotField, _
    FoundIt As Boolean

FoundIt = False
Set pT = ActiveSheet.PivotTables(1)

For Each pF In pT.PivotFields
    For Each pI In pF.PivotItems
        If pF.Name = PivotFieldName And pI.Name = PivotItemName Then
            'Ideally here would be the output of the address within the sheet
            On Error GoTo ErrHandler
            FoundIt = True
            LoopThroughPivotAndFindValue = pI.LabelRange.Address
            Exit For
            On Error GoTo 0
        End If
    Next pI
Next pF

If FoundIt Then GoTo FreeObjects
ErrHandler:
    LoopThroughPivotAndFindValue = "Nothing"

FreeObjects:
    Set pT = Nothing
End Function

So, I'd say the best way is to store the result into a temp variable and check if it is Nothing (in plain text, not in a parallel of objects) and then use the address to color it!

Upvotes: 1

John Bustos
John Bustos

Reputation: 19544

Without knowing exactly what your PivotTable looks like, I can't answer exactly, but I can tell you that what you're looking to use is the GetPivotData method where you can name the field and item you're looking to get and it will return a range.

Hope this does the trick!!

Upvotes: 2

Related Questions