CODEWITHSUNDEEP
excelpivot-tablevba
LWC
LWC

Reputation: 1200

Can a PivotField position be determined from a Selection?

If I select one of the PivotFields in the following PivotTable, is there a way to determine its position? For example, selecting Yc and deducing the position is 2.

Xa
---- Yb
---- Yc
-------- Za
-------- Zb
-------- Zc
Xb
---- Yd
-------- Ze
-------- Z5

Upvotes: 0

Views: 995

Answers (1)

PeterT
PeterT

Reputation: 8557

The key is to make sure you're in the right range. Review Jon Peltier's excellent page on pivot table ranges. In your case, you want to restrict your cell selection to RowFields. The path to figuring out which position is to back out from which Item in which Field...

Option Explicit

Sub WhatPosition()
    Dim pt As PivotTable
    Set pt = ActiveSheet.PivotTables(1)

    Debug.Print "current cell selection is " & Selection.Address & _
                " = '" & Selection.value & "'"

    '--- is the selection within the row label range of the pivot table?
    If Not Intersect(Selection, pt.RowRange) Is Nothing Then
        Debug.Print "selection position = " & StringToPivotFieldPosition(pt, Selection.value)
    Else
        Debug.Print "the selection is not within the pivot table"
    End If
End Sub

Private Function StringToPivotFieldPosition(ByRef pTable As PivotTable, _
                                            ByVal value As String) As Long
    Dim field As PivotField
    StringToPivotFieldPosition = 0
    For Each field In pTable.RowFields
        Dim item As PivotItem
        For Each item In field.PivotItems
            If item.Name = value Then
                StringToPivotFieldPosition = field.position
                Exit Function
            End If
        Next item
    Next field
End Function

Upvotes: 1

Related Questions