Macro Runtime Error 9: subscript out of range

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.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,414
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

its_meRajat

New Member
Joined
Feb 23, 2021
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
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.
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?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,414
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
The last chunk of code I posted would replace all the code you posted.
 

its_meRajat

New Member
Joined
Feb 23, 2021
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

The last chunk of code I posted would replace all the code you posted.

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
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,414
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
It looks like you are filling a lot of cells with links to the same cell on Sheet4 - why is that?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,414
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
 
Solution

its_meRajat

New Member
Joined
Feb 23, 2021
Messages
4
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
  3. Web
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
Thanks a lot, man. I can't thank you enough.
SOLVED.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,897
Messages
5,621,499
Members
415,845
Latest member
marej123

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
Top