时间戳

首页 > 解决方案 > 在数据透视表 vba 中设置和循环数据

excel - 在数据透视表 vba 中设置和循环数据

问题描述

我在工作表上有一个数据透视表，我想遍历其中一列，找到匹配信息并在右侧输入变量值 6 个单元格。我无法设置数据透视表。我收到“运行时错误 '438'：对象不支持此属性或方法”。当我注释掉这些代码行时，我得到一个“运行时错误'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

标签： excelvbapivot-table

解决方案

由于某种原因，我似乎也无法直接设置数据透视表。

假设您在该工作表上有一个数据透视表，则此方法有效。如果你有更多，只需调整索引号：

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")没有错误。

推荐阅读