Share via

I am trying to run VBA in excel. I'm getting a subscript out of range error that I can't figure out.

Anonymous
2023-04-29T03:57:58+00:00

Could someone please look at this simple macro script and give me a hand? Thanks in advance.

Note that it will run only once. Also note that to debug it you will have to manually remove the query it generates. I haven't added that bit yet.

Sub Test()

' 'Macro2 Macro

'

'

Range("M2").

Select Selection.Copy

ActiveWorkbook.Queries.Add Name:="Table 0", Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Web.Page(Web.Contents(""https://finance.yahoo.com/quote/SPYD/history""))," & Chr(13) & "" & Chr(10) & " Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""Date"", type date}, {""Open"", type text}, {""High"", type text}, {""Low"", type text}, {""Close*"", type text}, {""Adj Close**"", type text}, {""Volume"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type""" ActiveSheet.ListObjects("Table_ExternalData_1").Name = "Table_Table_0" ActiveWorkbook.Worksheets.Add

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0"";Extended Properties=""""" _

, Destination:=Range("$A$1")).QueryTable

.CommandType = xlCmdSql

.CommandText = Array("SELECT * FROM [Table 0]")

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.BackgroundQuery = True

.RefreshStyle = xlInsertDeleteCells

.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.PreserveColumnInfo = True

.ListObject.DisplayName = "Table_Table_0"

.Refresh BackgroundQuery:=False

End With

Range("F2").Select

Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = "38.17"

Sheets("Holdings").Select

Range("J2").Select

ActiveSheet.Paste

Range("J3").Select

ActiveWorkbook.Queries.Delete Name:="Table 0" ActiveSheet.ListObjects("Table_ExternalData_1").Name = "Table_Table_0" ActiveWorkbook.Worksheets.Delete End Sub

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-04-29T07:56:29+00:00

    The error "subscript out of range" in VBA usually occurs when the code is trying to reference an index that does not exist or is outside the defined range. Based on your code, there are a few possible reasons for the error.

    To fix this, you can replace the line: ActiveSheet.ListObjects("Table_ExternalData_1"). Name = "Table_Table_0"

    with:

    ActiveSheet.ListObjects(1). Name = "Table_Table_0"

    Also, you can replace the line: Range("M2"). Select with: ActiveSheet.Range("M2"). Select

    This ensures that the cell is selected in the active sheet.

    Please try this update code:

    Sub Test() ' ' Macro2 Macro ' ' ActiveSheet.Range("M2"). Select Selection.Copy ActiveWorkbook.Queries.Add Name:="Table 0", Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Web.Page(Web.Contents(""https://finance.yahoo.com/quote/SPYD/history""))," & Chr(13) & "" & Chr(10) & " Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""Date"", type date}, {""Open"", type text}, {""High"", type text}, {""Low"", type text}, {""Close*"", type text}, {""Adj Close**"", type text}, {""Volume"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type""" ActiveSheet.ListObjects(1). Name = "Table_Table_0" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$; Location=""Table 0""; Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable . CommandType = xlCmdSql . CommandText = Array("SELECT * FROM [Table 0]") . RowNumbers = False . FillAdjacentFormulas = False . PreserveFormatting = True . RefreshOnFileOpen = False . BackgroundQuery = True . RefreshStyle = xlInsertDeleteCells . SavePassword = False . SaveData = True . AdjustColumnWidth = True . RefreshPeriod = 0 . PreserveColumnInfo = True . ListObject.DisplayName = "Table_Table_0" . Refresh BackgroundQuery:=False End With ActiveSheet.Range("F2"). Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "38.17" Sheets("Holdings"). Select Range("J2"). Select

    Best Regards, Shakiru

    0 comments