在枢轴表vba中设置和循环数据

Question

我在工作表上有一个枢轴表，我想遍历其中一列，找到匹配的信息，然后在右边输入变量值6单元格。我无法设置枢轴表。我得到一个“运行时错误'438'：Object不支持这个属性或方法”。当我注释掉这些代码行时，我会得到一个“运行时错误‘91’：对象变量或块变量未设置”。我需要做些什么来设置枢轴表？

    Option Explicit
    Dim ProcRowCount As Integer
    Dim Process As String
    Dim ProcSID As String
    Dim ProcStat As String
    Dim ProcBeg As Date
    Dim ScheRow As Integer
    Dim ProcRow As Integer
    Dim OffName As String
    Dim DueDate As Date
    Dim SchEvent As String
    Dim EventSID As String
    Dim EventRow As Integer
    Dim Event2025 As String
    Dim EventOut As String
    Dim EventDate As Date
    Dim Eventdate2 As Date
    Dim NameSID As String
    Dim Pivotitem As PivotItems
    Dim Pivot As PivotTable
    Dim Pivotfield As Pivotfield

Private Sub EventReview()
'Loop though 2025
With ThisWorkbook.Worksheets("2025")
ScheRow = 2 'Worksheets("2025").Cells(Rows.Count, "a").End(xlUp).Row
EventRow = 2
EventSID = Worksheets("2025").Cells(EventRow, "a")
ProcSID = "7777777"
OffName = "E Off"

Do While EventRow <= ScheRow
    Event2025 = Worksheets("2025").Cells(EventRow, "j")
    EventOut = Worksheets("2025").Cells(EventRow, "Q")
    EventSID = Worksheets("2025").Cells(EventRow, "a")
    If ProcSID = EventSID And Event2025 = SchEvent And (EventOut = "Occur" Or EventOut = "OccVio") Then
        EventDate = Worksheets("2025").Cells(EventRow, "o")
        If Eventdate2 = "12:00:00 AM" Or Eventdate2 < EventDate Then
            Eventdate2 = EventDate
            End If
        EventRow = EventRow + 1
    Else: EventRow = EventRow + 1
    End If
Loop
End With

NameSID = OffName & " " & ProcSID

'loop through pivot table, insert date in offset column

With ThisWorkbook.Worksheets("Dashboard")
Set Pivot = Worksheets("Dashboard").PivotTable("ProcessPivot")  ***Error happens here***
Set Pivotfield = Pivot.PivotFields("HelperColumn").PivotItems
    For Each Pivotitem In Pivotfield.PivotItems
        If Pivotitem = NameSID And Eventdate2 <> "12:00:00 AM" Then
            Pivotitem.Offset(0, 6) = Eventdate2
        ElseIf Pivotitem = NameSID Then
            Pivotitem.Offset(0, 6) = "Not Reviewed"
        End If
    Next Pivotitem
End With

End Sub

Answer 1

由于某些原因，我似乎也不能直接设置数据透视表。

这是可行的，假设你有一个数据透视表在那张纸上。如果有更多信息，只需调整索引号：

Dim pivot
Set pivot = Worksheets("Dashboard").PivotTables(1)

Dim pvt
Set pvt = Worksheets("Dashboard").PivotTables(pivot.Name)
'pvt is now the Pivot table. You should be able to go from there.

编辑:好吧，嗯，我之前试过了，得到了一个错误，但是现在我可以只做没有错误的Dim pvt As PivotTable // Set pvt = Worksheets("Dashboard").PivotTables("ProcessPivot")了。