How to fix 'Run time error 9, subscript out of range'

Prateek Bhargava 0 Reputation points
2023-05-25T13:32:23.11+00:00

Created a Macro using 'Record Macros' and on running macro I am getting an error message 'Run time error 9, subscript out of range.' Following is the whole code, please suggest anyone-

Sub Test()
'
' Test Macro
'

'
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$B$17"), , xlNo).Name = _
        "Table1"
    Range("Table1[#All]").Select
    ActiveWorkbook.Queries.Add Name:="Table1", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Excel.CurrentWorkbook(){[Name=""Table1""]}[Content]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Split Column by Delimiter"" = Table.SplitColumn(#""Changed Type"", ""Column2"", Splitter.SplitTextByDelimiter("" "", QuoteStyle.Csv), {""Column2.1"", ""Column2.2"", ""Column2." & _
        "3"", ""Column2.4""})," & Chr(13) & "" & Chr(10) & "    #""Removed Columns"" = Table.RemoveColumns(#""Split Column by Delimiter"",{""Column2.1""})," & Chr(13) & "" & Chr(10) & "    #""Merged Columns"" = Table.CombineColumns(#""Removed Columns"",{""Column2.2"", ""Column2.3"", ""Column2.4""},Combiner.CombineTextByDelimiter("""", QuoteStyle.None),""Merged"")" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Merged Columns"""
    ActiveSheet.ListObjects("Table_ExternalData_1").Name = "Table_Table1"
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=Table1;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table1]")
        .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_Table1"
        .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.Save
End Sub
Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,707 questions
0 comments No comments
{count} votes