在VBA中引用PivotTable范围进行索引匹配

Sub IndexMatchWithPivotTable()
    Dim pt As PivotTable
    Dim dataRange As Range
    Dim indexValue As Variant
    Dim result As Variant
    
    ' 获取PivotTable对象
    Set pt = ThisWorkbook.Sheets("Sheet1").PivotTables("PivotTable1")
    
    ' 获取PivotTable的数据源范围
    Set dataRange = pt.DataBodyRange
    
    ' 设置要查找的值
    indexValue = "Apple"
    
    ' 在数据源范围中进行索引匹配
    result = Application.Match(indexValue, dataRange.Columns(1), 0)
    
    ' 判断是否找到匹配值
    If Not IsError(result) Then
        ' 找到匹配值，获取相应的相关值
        MsgBox dataRange.Cells(result, 2).Value
    Else
        ' 未找到匹配值
        MsgBox "Value not found."
    End If
End Sub