I have a pivot table created from 3 fields where (for example):
field1 values are: "1", "3", "5"
"A", "B", "C"
field3 values are: "X", "Y", "Z"
So given a pivot table PT1 I want to reference a cell within the table in a similar way to PT1["1"]["B"]["Y"]
and get a Range item returned. Is this possible? Thanks.
EDIT: codes:
Public Sub ColorIt2()
Dim rng As Range
For Each t_item In ActiveSheet.PivotTables("PivotTable1").PivotFields("F1").PivotItems
For Each r_item In ActiveSheet.PivotTables("PivotTable1").PivotFields("F2").PivotItems
For Each h_item In ActiveSheet.PivotTables("PivotTable1").PivotFields("F3").PivotItems
For Each b_item In ActiveSheet.PivotTables("PivotTable1").PivotFields("F4").PivotItems
If t_item.开发者_StackOverflow社区RecordCount <> 0 Or _
r_item.RecordCount <> 0 Or _
h_item.RecordCount <> 0 Or _
b_item.RecordCount <> 0 Then
Set rng = ActiveSheet.PivotTables("PivotTable1").GetPivotData(t_item, r_item, h_item, b_item)
rng.Select
Selection.Interior.ColorIndex = 40
Selection.Interior.Pattern = xlSolid
End If
End If
Next b_item
Next h_item
Next r_item
Next t_item
End Sub
Not sure, but maybe you want the GetPivotData function?
精彩评论