开发者

How to reference cells in a Pivot Table using Field values?

I have a pivot table created from 3 fields where (for example):

field1 values are: "1", "3", "5"

field2 values are: "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?

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜