Pivot Table Value Cell Address

Question

Is it possible to get the Cell Address (or Cell Range) of a value in a pivot table via VB?

Example:

Row Labels  Count   Threshold-%

REG1        224     0.00%
    FALSE   11
    TRUE    213

REG2        213     0.00%
    FALSE   13
    TRUE    200

REG3        318     0.00%
    FALSE   3
    TRUE    315

REG4        467     0.00%
    FALSE   7
    TRUE    460

Grand Total 1222    0.00%

How could I get the cell address of REG1 -> FALSE -> 11?

I know when I click on it, it gives me "B3" but I want to find it via VB.

Still looking, but it may help http://stackoverflow.com/questions/6262743/convert-cells1-1-into-a1-and-vice-versa — Destrif, May 27 '16 at 09:08
that helped! I managed to get the address I was looking for using the following code - pt.GetPivotData("State", "Region", "REG1", "State", False).Address — , May 27 '16 at 09:12

score 0 · Accepted Answer · edited May 23 '17 at 10:28

0

Yes you can, like this to get your pivot item:

https://msdn.microsoft.com/fr-fr/library/office/ff840045.aspx

https://msdn.microsoft.com/fr-fr/library/office/ff821539.aspx

After that you will find it's address with:

Convert cells(1,1) into "A1" and vice versa

or with pivot data:

https://msdn.microsoft.com/fr-fr/library/office/ff195919.aspx

edited May 23 '17 at 10:28

Community

1
1

answered May 27 '16 at 08:54

Destrif

2,104
1
14
22

Thank you for the links. I have read them but unfortunately it is not the value I am looking for, but the Cell Address. So something like "B3" etc. The range of the cell. Not the value inside it. – May 27 '16 at 08:56
The documentation said: The name of the field containing the data for the PivotTable. Note the data, that should return the range – Destrif May 27 '16 at 08:56
I'm not understanding. Would you have a code example? – May 27 '16 at 08:58

Vasily · Answer 2 · 2016-05-27T09:23:06.323

0

try this:

Sub test()
    Dim Data As Range, cl As Range
    Set Data = Range(Cells.Find("REG1").Offset(, 1), _
                     Cells.Find("REG2").Offset(-1, 2))

    For Each cl In Data
        If UCase(cl.Value2) = "FALSE" And cl.Offset(, 1).Value2 = 11 Then Exit For
    Next cl

    If Not cl Is Nothing Then MsgBox cl.Address
End Sub

test:

edited May 27 '16 at 09:23

answered May 27 '16 at 09:12

Vasily

5,707
3
19
34

Pivot Table Value Cell Address

2 Answers2