vba 运行时错误 9 消息下标超出范围

runtime error 9 message subscript out of range

提问by Glenn Low

I'm trying to create my first VBA code to basically: -select a cell -sort the column by A-Z -pivot the data -create a line chart -re-size the chart to make it bigger

我正在尝试创建我的第一个 VBA 代码，基本上： - 选择一个单元格 - 按 AZ 对列进行排序 - 透视数据 - 创建一个折线图 - 重新调整图表大小以使其更大

I'm getting a runtime error 9 message subscript out of rangeand it highlights the following line
ActiveWorkbook.Worksheets("advertiserConversionReport_3045").Sort.SortFields. _ Clear

我收到一条运行时错误 9 消息subscript out of range，它突出显示了以下行
ActiveWorkbook.Worksheets("advertiserConversionReport_3045").Sort.SortFields。_ 清除

What I did was to open another workbook and tried to run the macro on the data. Looking through the code (I'm not a developer) I'm seeing that the errors could be the reference to the initial workbook advertiserConversionReport_3045and perhaps a previously set range for the pivot Range("A2:F97")

我所做的是打开另一个工作簿并尝试对数据运行宏。查看代码（我不是开发人员）我看到错误可能是对初始工作簿的引用，advertiserConversionReport_3045也可能是先前设置的枢轴范围Range("A2:F97")

Sub ActionReport()
'
' ActionReport Macro
' This macro will pivot data from the action report and create a line chart to show     trending of credited conversions by day.
'
' Keyboard Shortcut: Ctrl+shift+a
'
Range("B1").Select
ActiveWorkbook.Worksheets("advertiserConversionReport_3045").Sort.SortFields. _
    Clear
ActiveWorkbook.Worksheets("advertiserConversionReport_3045").Sort.SortFields. _
    Add Key:=Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, _
    DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("advertiserConversionReport_3045").Sort
    .SetRange Range("A2:F97")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    "advertiserConversionReport_3045!R1C1:R97C6", Version:=xlPivotTableVersion14) _
    .CreatePivotTable TableDestination:="Sheet1!R3C1", TableName:="PivotTable1" _
    , DefaultVersion:=xlPivotTableVersion14
Sheets("Sheet1").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Day")
    .Orientation = xlRowField
    .Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Conversion Action")
    .Orientation = xlRowField
    .Position = 2
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
    "PivotTable1").PivotFields("Credited Conversions"), _
    "Sum of Credited Conversions", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Conversion Action")
    .Orientation = xlColumnField
    .Position = 1
End With
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Range("Sheet1!$A:$R")
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.6583333333, msoFalse, _
    msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 1").ScaleHeight 1.7065974045, msoFalse, _
    msoScaleFromTopLeft
ActiveWindow.SmallScroll Down:=-24
ActiveSheet.Shapes("Chart 1").IncrementLeft -38.25
ActiveSheet.Shapes("Chart 1").IncrementTop -81.75
End Sub`

Does anyone know how to fix this and execute the macro successfully?

有谁知道如何解决这个问题并成功执行宏？

回答by Siddharth Rout

You are getting that error because the "Activeworkbook" doesn't have the sheet called "advertiserConversionReport_3045".

您收到该错误是因为“Activeworkbook”没有名为“advertiserConversionReport_3045”的工作表。

Avoid using ActiveWorkbook. Create Objects and then work with them. You may want to see this link

避免使用 ActiveWorkbook。创建对象，然后使用它们。你可能想看看这个链接

Try something like this

尝试这样的事情

Sub Sample()
    Dim thiswb As Workbook, wbNew As Workbook

    Set thiswb = ThisWorkbook

    '~~> Change as applicable
    Set wbNew = Workbooks.Open("C:\Sample.xlsm")

    With wbNew
        .Worksheets("advertiserConversionReport_3045").Sort.SortFields.Clear
    End With
End Sub