首页 文章

Excel调试VBA“运行时错误'424'：需要对象”

提问于
浏览 次
0

使用宏通过单击按钮创建数据透视表 . 我每天都下载新的报告，所以每次都是不同的Excel工作表和工作表名称 .

我设法通过简单地每次重命名相关选项卡“数据”来解决这个事实，它是一个不同的工作簿名称 . 我已经得到它来创建我想要的数据透视表，除了它给我“Count”而不是像我需要的“Sum” . 我得到的错误是

运行时错误'424'：需要对象

突出显示的行是 .Position = 1 接近结束，我在这里打破了 .

Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
    Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1 _
    ), Array(14, 1), Array(15, 1))
Cells.Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "data!R1C1:R1048576C15", Version:=xlPivotTableVersion14).CreatePivotTable _
    TableDestination:="Sheet1!R3C1", TableName:="PivotTable2", DefaultVersion _
    :=xlPivotTableVersion14
Sheets("Sheet1").Select
Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
    "PivotTable2").PivotFields("Date"), "Count of Date", xlCount
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
    "PivotTable2").PivotFields("Store Listing Visitors"), _
    "Count of Store Listing Visitors", xlCount
ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
    "PivotTable2").PivotFields("Installers"), "Count of Installers", xlCount
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Count of Date")
    .Orientation = xlRowField
    .Position = 1
End With
ExecuteExcel4Macro _
    "PIVOT.FIELD.PROPERTIES(""PivotTable2"",""Count of Store Listing Visitors"",,,2)"
ExecuteExcel4Macro _
    "PIVOT.FIELD.PROPERTIES(""PivotTable2"",""Count of Installers"",,,2)"

我提前感谢您的帮助！

excel vba excel-vba

1 回答

  • 0

    这是对代码的重写

    如果您以更清晰的方式格式化代码，我认为您可以更轻松地进行调试 .

    Sub pivTest()

    Columns("A:A").TextToColumns _
        Destination:=Range("A1"), _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, _
        ConsecutiveDelimiter:=False, _
        Tab:=True, _
        Semicolon:=False, _
        Comma:=True, _
        Space:=False, _
        Other:=False, _
        FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), _
                         Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
                         Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), _
                         Array(13, 1), Array(14, 1), Array(15, 1))

'   Cells.Select  ' do not use

    Sheets.Add    ' what is the name of this sheet ?

    ActiveWorkbook.PivotCaches.Create( _
            SourceType:=xlDatabase, _
            SourceData:="data!R1C1:R1048576C15", _
            Version:=xlPivotTableVersion14).CreatePivotTable _
                                                    TableDestination:="Sheet2!R3C1", _
                                                    TableName:="PivotTable2", _
                                                    DefaultVersion:=xlPivotTableVersion14
'   Sheets("Sheet1").Select   ' do not use

'   Cells(3, 1).Select        ' do not use

    Dim pt As PivotTable
    Set pt = Sheets("Sheet1").PivotTables("PivotTable2")

    pt.AddDataField pt.PivotFields("Date"), "Count of Date", xlCount
    pt.AddDataField pt.PivotFields("Store Listing Visitors"), "Count of Store Listing Visitors", xlCount
    pt.AddDataField pt.PivotFields("Installers"), "Count of Installers", xlCount

    pt.PivotFields("Count of Date").Orientation = xlRowField
    pt.PivotFields("Date").Position = 1           ' it is not "Count of Date" anymore, because previous line moved it out of the "Sum Values"

    ExecuteExcel4Macro "PIVOT.FIELD.PROPERTIES(""PivotTable2"",""Count of Store Listing Visitors"",,,2)"
    ExecuteExcel4Macro "PIVOT.FIELD.PROPERTIES(""PivotTable2"",""Count of Installers"",,,2)"

End Sub
    回复于

相关问题