Excel宏运行时错误424
问题描述:
我无法找到我的运行时错误424的根本原因。我知道这是与一个缺失的对象有关,但我不确定哪里或哪个对象甚至会在这种情况下。我的假设是与ActiveSheet有关,但我有点失落。Excel宏运行时错误424
Sub Macro1()
'
' Macro1 Macro
'
'
Sheets.Add
错误这里就从这里开始
PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
ActiveSheet.Range("A1").CurrentRegion.Select, Version:= _
xlPivotTableVersion12).CreatePivotTable TableDestination:="Sheet1!R3C1", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
错误结束
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Source Type")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Category")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Category").Orientation = _
xlHidden
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Activity")
.Orientation = xlRowField
.Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables(_
"PivotTable1").PivotFields("USD Amount"), "Sum of USD Amount", xlSum
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables(_
"PivotTable1").PivotFields("Quantity"), "Sum of Quantity", xlSum
End Sub
答
我很少需要创建透视表编程如此,让这是怎么回事我创建了一个小分来逐步了解:
Sub CreatePivotOnNewSheet() Sheets.Add ActiveSheet.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ ActiveSheet.Range("A1").CurrentRegion, Version:= _ xlPivotTableVersion12).CreatePivotTable TableDestination:="Sheet1!R3C1", _ TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12 End Sub
This compiles OK but throws an error 438: Object doesn't support this property or method. I searched the Object Browser for PivotCaches and found that it is a member of Workbook and PivotTable. It isn't a member of Worksheets! This makes sense when you consider that pivot tables on different sheets can use the same pivot cache.
所以,改变代码:
Sub CreatePivotOnNewSheet()
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
ActiveSheet.Range("A1").CurrentRegion, Version:= _
xlPivotTableVersion12).CreatePivotTable TableDestination:="Sheet1!R3C1", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12
End Sub
,现在我们得到一个运行时错误:
哪个需要更大才能显示完整的原因(谢谢微软!),但我想它继续说对至少包含两行的范围的引用,并且其顶部行中没有空白单元格。在中断模式下,我去了新添加的工作表并创建了一个2x2的数据表。回到IDE中,按下F8并且它工作正常,尽管数据透视表没有定义任何行/列等。我没有试图测试你的剩余代码,这似乎创造了定义 - 我会把它留给你。
答
它与录制的宏的问题,有没有在所有定义的对象。
我建议用变量替换所有“活动”短语。这里是开始:
Dim wb As Workbook
Set wb = ActiveWorkbook
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = wb.Sheets(1)
Set ws2 = wb.Sheets(2)
Dim Caches As PivotCache
Set Caches = wb.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
ws2.Range("A1:C3"), Version:= _
xlPivotTableVersion12)