Excel VBA运行时错误'91'

问题描述：

我正在尝试使用VBA创建数据透视表，并且我从@Shai Rado获得了帮助，以便能够创建一个空的数据透视表，然后添加值字段，当我尝试再次运行它时，出现以下错误：对象变量或块变量未设置为“代码”使用.PivotFields（“MedID”）“Excel VBA运行时错误'91'

这对我没有意义，因为那部分代码已经创建了一个空的数据透视表和现在它显示错误。

有什么想法？我完全陌生的VBA，所以我有很多“傻”的问题。我非常感谢所有帮助！

Option Explicit 

Sub Create_Pivot_Table() 

Dim wsData As Worksheet, wsPT As Worksheet 
Dim PT_Cache As PivotCache 
Dim PT   As PivotTable 
Dim PRng  As Range 
Dim LastRow  As Long 

With ThisWorkbook 
    Set wsData = .Worksheets("Data") 
    Set wsPT = .Worksheets("Pivot Table") 
End With 

LastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row 
MsgBox LastRow ' <-- confirm value 

Set PRng = wsData.Range("A1:O" & LastRow) 

' option 2: Set the Pivot Cache 
Set PT_Cache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRng.Address(True, True, xlR1C1, True)) 

' set the Pivot Table 
Set PT = PT_Cache.CreatePivotTable(wsPT.Range("D5"), "Pivot_Table_Test") 

With PT 



    With .PivotFields("MedID") 
     .Orientation = xlRowField 
     .Position = 1 
     .LayoutBlankLine = False 
     .Subtotals(1) = False 
    End With 

    With .PivotFields("TransactionType") 
     .Orientation = xlColumnField 
     .Position = 1 
     .LayoutBlankLine = False 
     .Subtotals(1) = False 

    End With 


    With .PivotFields("Quantity") 
     .Orientation = xlDataField 
     .Function = xlCount 
     .Position = 1 

    End With 

Set PT = Nothing 
Set PT_Cache = Nothing 
Set wsData = Nothing 
Set wsPT = Nothing 
Exit Sub 

End With 

End Sub

好像对象PT为空。确保这一行：设置PT = CreatePivotTable（....正在返回一个值并设置PT。 – aguertin

也尝试删除pivottable作为第一行代码（如果存在）。我想知道它不工作的原因是因为它已经存在 – aguertin

@aguertin如果它存在就不需要删除'PivotTable'，只是为了检查它是否是，如果它确实存在，那么你所需要的就是用更新的'PivotCache'来更新它，如果它不是那么添加'PivotTable' –

答

尝试更新的代码如下（以满足您的需要）：

Option Explicit 

Sub Create_Pivot_Table() 

Dim wsData As Worksheet, wsPT As Worksheet 
Dim PT_Cache As PivotCache 
Dim PT   As PivotTable 
Dim PRng  As Range 
Dim LastRow  As Long 

With ThisWorkbook 
    Set wsData = .Worksheets("Data") 
    Set wsPT = .Worksheets("Pivot Table") 
End With 

LastRow = wsData.Cells(wsData.Rows.Count, 1).End(xlUp).Row  
Set PRng = wsData.Range("A1:O" & LastRow) 

' Set the Pivot Cache 
Set PT_Cache = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=PRng.Address(True, True, xlR1C1, True)) 

' add this line in case the Pivot table doesn't exit >> first time running this Macro 
On Error Resume Next 
Set PT = wsPT.PivotTables("Pivot_Table_Test") ' check if "Pivot_Table_Test" Pivot Table already created (in past runs of this Macro) 

On Error GoTo 0 
If PT Is Nothing Then 
    ' create a new Pivot Table in "Pivot Table" sheet, start from Cell D5 
    Set PT = wsPT.PivotTables.Add(PivotCache:=PT_Cache, TableDestination:=wsPT.Range("D5"), TableName:="Pivot_Table_Test") 

Else ' just refresh the Pivot cache with the updated Range in "Data" sheet 
    PT.ChangePivotCache PT_Cache 
    PT.RefreshTable 
End If 

' === set Pivot Table fields === 
With PT 
    With .PivotFields("MedID") 
     .Orientation = xlRowField 
     .Position = 1 
     .LayoutBlankLine = False 
     .Subtotals(1) = False 
    End With 
    With .PivotFields("TransactionType") 
     .Orientation = xlColumnField 
     .Position = 1 
     .LayoutBlankLine = False 
     .Subtotals(1) = False 
    End With 
    With .PivotFields("Quantity") 
     .Orientation = xlDataField 
     .Function = xlCount 
     .Position = 1 
    End With 
End With 

Set PT = Nothing 
Set PT_Cache = Nothing 
Set wsData = Nothing 
Set wsPT = Nothing 
Exit Sub 

End Sub

这是完美的！谢谢！ –

答

有了这个错误，它看起来像PT没有设置为数据透视表，所以没有什么可引用像With .PivotFields("MedID")。创建PT与名指PT到您的数据透视表像Set PT = ThisWorkbook.PivotTables("PivotName")

Excel VBA运行时错误'91'

相关推荐