Macro Runtime Error 9: subscript out of range

I

its_meRajat

New Member
Joined
Feb 23, 2021
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
Hi,
Disclaimer: I am using Excel 365 and I have zero programming knowledge or experience.
Today I used Macros for the first time and recorded a Macro to pull bank fixed deposit rates from a bank website and input data from this table to another one.
But when I run this Macro, I get a Runtime Error 9: subscript out of range.
See the below error:
1614068030118.png

I really need to do this for a project but I can't understand anything with this yellow highlighted code. Any help will be much appreciated. I apologise if I am not following some rules of this community or if am asking something really lame.
Have a great day ahead.
Thank You for your time and help.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Welcome to the forum. :)

It just means that there isn't a sheet called Sheet2. I'm guessing you want it to refer to the sheet that was added? There is no guarantee that that will be called Sheet2 so you should use a variable, like this:

Code: 
Dim ws as Worksheet
Set ws = Sheets.Add(After:=Activesheet)

then instead of trying to use the sheet name, which will vary, you can use:

Code: 
ws.Select

However, there is rarely any need to select anything in code, so you could replace your code with this:

Code: 
Dim ws as Worksheet
Set ws = Sheets.Add(After:=Activesheet)
With Sheets("Sheet1")
    .Range("A5", .Range("A5").End(xlDown)).Copy Destination:=ws.Range("A4")
End With
ws.Columns("A:A").Entirecolumn.Autofit

for example.

Also, for future reference, it's a lot easier for us to help if you post the actual code as text, not a picture.
 
Upvote 0
RoryA said:
Welcome to the forum. :)

It just means that there isn't a sheet called Sheet2. I'm guessing you want it to refer to the sheet that was added? There is no guarantee that that will be called Sheet2 so you should use a variable, like this:

Code: 
Dim ws as Worksheet
Set ws = Sheets.Add(After:=Activesheet)

then instead of trying to use the sheet name, which will vary, you can use:

Code: 
ws.Select

However, there is rarely any need to select anything in code, so you could replace your code with this:

Code: 
Dim ws as Worksheet
Set ws = Sheets.Add(After:=Activesheet)
With Sheets("Sheet1")
    .Range("A5", .Range("A5").End(xlDown)).Copy Destination:=ws.Range("A4")
End With
ws.Columns("A:A").Entirecolumn.Autofit

for example.

Also, for future reference, it's a lot easier for us to help if you post the actual code as text, not a picture.
Click to expand...
Thanks a lot. I will try it with this. From next time I will make sure to paste the code instead of the screenshot.
I have one more doubt, should I replace the yellow part of my code with this one that you have given?
 
Upvote 0
The last chunk of code I posted would replace all the code you posted.
 
Upvote 0
RoryA said:
The last chunk of code I posted would replace all the code you posted.
Click to expand...

My Bad. Now I can understand the confusion. In my first query, I only posted that part of the code which was highlighted yellow for debugging. Here is the complete code which was generated by the Macro I recorded. I have replaced the initial part with your code.
This is the weblink from where I have to load the table 0: FD Interest Rates - Check Latest Fixed Deposit Interest Rates Online - ICICI Bank
And then I have to take values from this new table to another table which is already made in Excel.
This is the whole Macro that I recorded.
Also, I deeply apologise for not putting the whole code, the first time itself. My amateur and panicked *** begs your forgiveness.
Thanks a lot for your time and help.


VBA Code: 
Sub Macro1ICICI()
'
' Macro1ICICI Macro
'

'
    Dim ws As Worksheet
Set ws = Sheets.Add(After:=ActiveSheet)
With Sheets("Sheet1")
    .Range("A5", .Range("A5").End(xlDown)).Copy Destination:=ws.Range("A4")
End With
ws.Columns("A:A").EntireColumn.AutoFit
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "ICICI"
    Range("B5").Select
    Sheets.Add After:=ActiveSheet
    ActiveWorkbook.Queries.Add Name:="Table 0 (2)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://www.icicibank.com/Personal-Banking/account-deposit/fixed-deposit/fd-interest-rates.page""))," & Chr(13) & "" & Chr(10) & "    Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""Maturity Period"", type text}, {""Interest rates (per cent per annum) w.e.f. Oct 21, 2020 Single deposit of less than ? 20.0 million " & _
        "General"", type text}, {""Interest rates (per cent per annum) w.e.f. Oct 21, 2020 Single deposit of less than ? 20.0 million **Senior Citizen"", type text}, {""Interest rates (per cent per annum) w.e.f. February 17, 2021 Single deposit of ? 20.0 mn & above but less than 50.0 mn General"", type text}, {""Interest rates (per cent per annum) w.e.f. February 17, 2021 Si" & _
        "ngle deposit of ? 20.0 mn & above but less than 50.0 mn **Senior Citizen"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0 (2)"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0 (2)]")
        .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_0__2"
        .Refresh BackgroundQuery:=False
    End With
    Range("B10").Select
    Application.CommandBars("Queries and Connections").Visible = False
    Sheets("Sheet2").Select
    ActiveCell.FormulaR1C1 = _
        "=Table_0__2[@[Interest rates (per cent per annum) w.e.f. Oct 21, 2020 Single deposit of less than ? 20.0 million General]]"
    Range("B6").Select
    ActiveCell.FormulaR1C1 = _
        "=Table_0__2[@[Interest rates (per cent per annum) w.e.f. Oct 21, 2020 Single deposit of less than ? 20.0 million General]]"
    Range("B7").Select
    ActiveCell.FormulaR1C1 = _
        "=Table_0__2[@[Interest rates (per cent per annum) w.e.f. Oct 21, 2020 Single deposit of less than ? 20.0 million General]]"
    Range("B8").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-1]C"
    Range("B9").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-1]C"
    Range("B10").Select
    Range("B20").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-11]C"
    Range("B21").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-11]C"
    Range("B22").Select
    ActiveWindow.SmallScroll Down:=13
    Sheets("Sheet2").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-11]C"
    Range("B23").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-12]C"
    Range("B24").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-13]C"
    Range("B25").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-13]C"
    Range("B26").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-13]C"
    Range("B27").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-14]C"
    Range("B28").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-14]C"
    Range("B29").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-14]C"
    Range("B30").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-15]C"
    Range("B31").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-16]C"
    Range("B32").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-16]C"
    Range("B33").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-17]C"
    Range("B34").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-18]C"
    Range("B35").Select
    ActiveWindow.SmallScroll Down:=6
    Range("B32").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R16C2"
    Range("B32").Select
    Selection.AutoFill Destination:=Range("B32:B37"), Type:=xlFillDefault
    Range("B32:B37").Select
    Range("B38").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-21]C"
    Range("B39").Select
    ActiveWindow.SmallScroll Down:=8
    Sheets("Sheet4").Select
    Range("B11").Select
    ActiveWindow.SmallScroll Down:=5
    Sheets("Sheet2").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-23]C"
    Range("B40").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-24]C"
    Range("B41").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-24]C"
    Range("B42").Select
    ActiveWindow.SmallScroll Down:=4
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-27]C"
    Range("B43").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-27]C"
    Range("B44").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-28]C"
    Range("B45").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-29]C"
    Range("B46").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-29]C"
    Range("B46").Select
    Sheets("Sheet4").Select
    Range("B17").Select
    Sheets("Sheet2").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R17C2"
    Range("B46").Select
    Selection.AutoFill Destination:=Range("B46:B51"), Type:=xlFillDefault
    Range("B46:B51").Select
    Range("B52").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-34]C"
    Range("B53").Select
    ActiveWindow.SmallScroll Down:=7
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-36]C"
    Range("B54").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-36]C"
    Range("B55").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-37]C"
    Range("B56").Select
    ActiveWindow.SmallScroll Down:=14
    Range("B64").Select
    ActiveWindow.SmallScroll Down:=8
    Sheets("Sheet2").Select
    Range("B78").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-57]C"
    Range("B77").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-57]C"
    Range("B76").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-56]C"
    Range("B75").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-55]C"
    Range("B74").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-54]C"
    Range("B73").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-54]C"
    Range("B72").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-53]C"
    Range("B71").Select
    Sheets("Sheet4").Select
    Range("B19").Select
    Sheets("Sheet2").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-52]C"
    Range("B70").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-51]C"
    Range("B69").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-51]C"
    Range("B68").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-50]C"
    Range("B67").Select
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-49]C"
    Range("B66").Select
    ActiveWindow.SmallScroll Down:=-7
    ActiveCell.FormulaR1C1 = "=Sheet4!R[-48]C"
    Range("B65").Select
End Sub
 
Upvote 0
It looks like you are filling a lot of cells with links to the same cell on Sheet4 - why is that?
 
Upvote 0
It's always a pain cleaning up recorded code, but I think this is equivalent:

VBA Code: 
Sub Macro1ICICI()
'
' Macro1ICICI Macro
'

'
    Dim ws As Worksheet
    Set ws = Sheets.Add(After:=ActiveSheet)
    With Sheets("Sheet1")
        .Range("A5", .Range("A5").End(xlDown)).Copy Destination:=ws.Range("A4")
    End With
    With ws
        .Columns("A:A").EntireColumn.AutoFit
        .Range("B4").Value = "ICICI"
    End With
    Sheets.Add After:=ActiveSheet
    ActiveWorkbook.Queries.Add Name:="Table 0 (2)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://www.icicibank.com/Personal-Banking/account-deposit/fixed-deposit/fd-interest-rates.page""))," & Chr(13) & "" & Chr(10) & "    Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""Maturity Period"", type text}, {""Interest rates (per cent per annum) w.e.f. Oct 21, 2020 Single deposit of less than ? 20.0 million " & _
        "General"", type text}, {""Interest rates (per cent per annum) w.e.f. Oct 21, 2020 Single deposit of less than ? 20.0 million **Senior Citizen"", type text}, {""Interest rates (per cent per annum) w.e.f. February 17, 2021 Single deposit of ? 20.0 mn & above but less than 50.0 mn General"", type text}, {""Interest rates (per cent per annum) w.e.f. February 17, 2021 Si" & _
        "ngle deposit of ? 20.0 mn & above but less than 50.0 mn **Senior Citizen"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    Dim wsQuery As Worksheet
    Set wsQuery = ActiveWorkbook.Worksheets.Add
    wsQuery.Name = "Web_query"
    With wsQuery.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0 (2)"";Extended Properties=""""" _
        , Destination:=wsQuery.Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0 (2)]")
        .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_0__2"
        .Refresh BackgroundQuery:=False
    End With
    Application.CommandBars("Queries and Connections").Visible = False
    With ws
        .Range("B5:B7").Formula = _
        "=Table_0__2[@[Interest rates (per cent per annum) w.e.f. Oct 21, 2020 Single deposit of less than ? 20.0 million General]]"
        .Range("B8").FormulaR1C1 = "=Web_query!R[-1]C"
        .Range("B9").FormulaR1C1 = "=Web_query!R[-1]C"
        .Range("B20:B22").FormulaR1C1 = "=Web_query!R[-11]C"
        .Range("B23").FormulaR1C1 = "=Web_query!R[-12]C"
        .Range("B24:B26").FormulaR1C1 = "=Web_query!R[-13]C"
        .Range("B27:B29").FormulaR1C1 = "=Web_query!R[-14]C"
        .Range("B30").FormulaR1C1 = "=Web_query!R[-15]C"
        .Range("B31:B32").FormulaR1C1 = "=Web_query!R[-16]C"
        .Range("B33").FormulaR1C1 = "=Web_query!R[-17]C"
        .Range("B34").FormulaR1C1 = "=Web_query!R[-18]C"
        .Range("B32").FormulaR1C1 = "=Web_query!R16C2"
        .Range("B32").AutoFill Destination:=.Range("B32:B37"), Type:=xlFillDefault
        .Range("B38").FormulaR1C1 = "=Web_query!R[-21]C"
        .Range("B39").FormulaR1C1 = "=Web_query!R[-23]C"
        .Range("B40:B41").FormulaR1C1 = "=Web_query!R[-24]C"
        .Range("B42:B43").FormulaR1C1 = "=Web_query!R[-27]C"
        .Range("B44").FormulaR1C1 = "=Web_query!R[-28]C"
        .Range("B45:B46").FormulaR1C1 = "=Web_query!R[-29]C"
        .Range("B46").FormulaR1C1 = "=Web_query!R17C2"
        .Range("B46").AutoFill Destination:=.Range("B46:B51"), Type:=xlFillDefault
        .Range("B52").FormulaR1C1 = "=Web_query!R[-34]C"
        .Range("B53:B54").FormulaR1C1 = "=Web_query!R[-36]C"
        .Range("B55").FormulaR1C1 = "=Web_query!R[-37]C"
        .Range("B77:B78").FormulaR1C1 = "=Web_query!R[-57]C"
        .Range("B76").FormulaR1C1 = "=Web_query!R[-56]C"
        .Range("B75").FormulaR1C1 = "=Web_query!R[-55]C"
        .Range("B73:B74").FormulaR1C1 = "=Web_query!R[-54]C"
        .Range("B72").FormulaR1C1 = "=Web_query!R[-53]C"
        .Range("B71").FormulaR1C1 = "=Web_query!R[-52]C"
        .Range("B69:B70").FormulaR1C1 = "=Web_query!R[-51]C"
        .Range("B68").FormulaR1C1 = "=Web_query!R[-50]C"
        .Range("B67").FormulaR1C1 = "=Web_query!R[-49]C"
        .Range("B66").FormulaR1C1 = "=Web_query!R[-48]C"
    End With
End Sub
 
Upvote 0
Solution
RoryA said:
It's always a pain cleaning up recorded code, but I think this is equivalent:

VBA Code: 
Sub Macro1ICICI()
'
' Macro1ICICI Macro
'

'
    Dim ws As Worksheet
    Set ws = Sheets.Add(After:=ActiveSheet)
    With Sheets("Sheet1")
        .Range("A5", .Range("A5").End(xlDown)).Copy Destination:=ws.Range("A4")
    End With
    With ws
        .Columns("A:A").EntireColumn.AutoFit
        .Range("B4").Value = "ICICI"
    End With
    Sheets.Add After:=ActiveSheet
    ActiveWorkbook.Queries.Add Name:="Table 0 (2)", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Web.Page(Web.Contents(""https://www.icicibank.com/Personal-Banking/account-deposit/fixed-deposit/fd-interest-rates.page""))," & Chr(13) & "" & Chr(10) & "    Data0 = Source{0}[Data]," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(Data0,{{""Maturity Period"", type text}, {""Interest rates (per cent per annum) w.e.f. Oct 21, 2020 Single deposit of less than ? 20.0 million " & _
        "General"", type text}, {""Interest rates (per cent per annum) w.e.f. Oct 21, 2020 Single deposit of less than ? 20.0 million **Senior Citizen"", type text}, {""Interest rates (per cent per annum) w.e.f. February 17, 2021 Single deposit of ? 20.0 mn & above but less than 50.0 mn General"", type text}, {""Interest rates (per cent per annum) w.e.f. February 17, 2021 Si" & _
        "ngle deposit of ? 20.0 mn & above but less than 50.0 mn **Senior Citizen"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    Dim wsQuery As Worksheet
    Set wsQuery = ActiveWorkbook.Worksheets.Add
    wsQuery.Name = "Web_query"
    With wsQuery.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Table 0 (2)"";Extended Properties=""""" _
        , Destination:=wsQuery.Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [Table 0 (2)]")
        .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_0__2"
        .Refresh BackgroundQuery:=False
    End With
    Application.CommandBars("Queries and Connections").Visible = False
    With ws
        .Range("B5:B7").Formula = _
        "=Table_0__2[@[Interest rates (per cent per annum) w.e.f. Oct 21, 2020 Single deposit of less than ? 20.0 million General]]"
        .Range("B8").FormulaR1C1 = "=Web_query!R[-1]C"
        .Range("B9").FormulaR1C1 = "=Web_query!R[-1]C"
        .Range("B20:B22").FormulaR1C1 = "=Web_query!R[-11]C"
        .Range("B23").FormulaR1C1 = "=Web_query!R[-12]C"
        .Range("B24:B26").FormulaR1C1 = "=Web_query!R[-13]C"
        .Range("B27:B29").FormulaR1C1 = "=Web_query!R[-14]C"
        .Range("B30").FormulaR1C1 = "=Web_query!R[-15]C"
        .Range("B31:B32").FormulaR1C1 = "=Web_query!R[-16]C"
        .Range("B33").FormulaR1C1 = "=Web_query!R[-17]C"
        .Range("B34").FormulaR1C1 = "=Web_query!R[-18]C"
        .Range("B32").FormulaR1C1 = "=Web_query!R16C2"
        .Range("B32").AutoFill Destination:=.Range("B32:B37"), Type:=xlFillDefault
        .Range("B38").FormulaR1C1 = "=Web_query!R[-21]C"
        .Range("B39").FormulaR1C1 = "=Web_query!R[-23]C"
        .Range("B40:B41").FormulaR1C1 = "=Web_query!R[-24]C"
        .Range("B42:B43").FormulaR1C1 = "=Web_query!R[-27]C"
        .Range("B44").FormulaR1C1 = "=Web_query!R[-28]C"
        .Range("B45:B46").FormulaR1C1 = "=Web_query!R[-29]C"
        .Range("B46").FormulaR1C1 = "=Web_query!R17C2"
        .Range("B46").AutoFill Destination:=.Range("B46:B51"), Type:=xlFillDefault
        .Range("B52").FormulaR1C1 = "=Web_query!R[-34]C"
        .Range("B53:B54").FormulaR1C1 = "=Web_query!R[-36]C"
        .Range("B55").FormulaR1C1 = "=Web_query!R[-37]C"
        .Range("B77:B78").FormulaR1C1 = "=Web_query!R[-57]C"
        .Range("B76").FormulaR1C1 = "=Web_query!R[-56]C"
        .Range("B75").FormulaR1C1 = "=Web_query!R[-55]C"
        .Range("B73:B74").FormulaR1C1 = "=Web_query!R[-54]C"
        .Range("B72").FormulaR1C1 = "=Web_query!R[-53]C"
        .Range("B71").FormulaR1C1 = "=Web_query!R[-52]C"
        .Range("B69:B70").FormulaR1C1 = "=Web_query!R[-51]C"
        .Range("B68").FormulaR1C1 = "=Web_query!R[-50]C"
        .Range("B67").FormulaR1C1 = "=Web_query!R[-49]C"
        .Range("B66").FormulaR1C1 = "=Web_query!R[-48]C"
    End With
End Sub
Click to expand...
Thanks a lot, man. I can't thank you enough.
SOLVED.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Run-Time Error '9'; Subscript out of range
Replies
2
Views
260
Roland Hoelscher
R
A
  • Solved
Subscript out of range vba error 9 excel, but it works on other laptops
2
Replies
15
Views
904
smozgur
smozgur
C
  • Question Question
Working with multiple workbooks
Replies
4
Views
87
CT56
C
C
  • Solved
Runtime Error 9 Subscript Out of Range ... sometmes?
2
Replies
13
Views
912
ColoKevMan
C
D
  • Solved
Could you please help me to write a macro for this situation?
Replies
6
Views
286
jolivanes
jolivanes

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top