Relative reference for Macros

Question

I am new to macros, and to recording them. I created a webquery with a parameter that it edits a part of the query based on a cell I select. I now have to run this web query 800+ times. So I recorded myself doing it with the "Use relative references" checked. But it always puts the webquery in the same cell I recorded the macro on, not the in the cell next to the cell I select for the web query.

Ex: I have a query run in A2 based on a reference in A1. So, I want the macro to run the query by using the information from B1 and put it into B2, but it always puts it into A2.

The code!

With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Users\alillien.ASSOCIATED_NT\AppData\Roaming\Microsoft \Queries\990Finder.iqy" _
    , Destination:=Range("$C$576"))
    .Name = "990 Finder_284"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = False
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingAll
    .WebTables = """MainContent_GridView1"""
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With
ActiveCell.Offset(2, 0).Range("A1").Select
End Sub

I know it is because of the "Destination:=Range("$C$576")" line, but I don't know how to edit it to be relative to my starting point/where I click for my adjustable query.

Thank you very much!

Answer 1

OK, you could try this.

Replace:

Range("$C$576")

With:

ActiveCell.Offset(1,0)

This will effectively make the range referenced currently as $C$576 change to the active cell in the sheet, but one cell down (which I think is what you want). If you want one cell right, change it to (0,1)

Answer 2

RC notation is not allowed in the context that you want to use it I believe. I tried to and it didnt work, but you can use cells object in the same manner.

Create some variables to hold the current row and column that your in.

With those you can apply it to the "Destination:=Range(Cells(curRow + 1, curCol).Address)" section of your code:

Dim curRow As Integer
Dim curCol As Integer
curRow = ActiveCell.Row
curCol = ActiveCell.Column
    With ActiveSheet.QueryTables.Add(Connection:= _
"FINDER;C:\Users\alillien.ASSOCIATED_NT\AppData\Roaming\Microsoft \Queries\990Finder.iqy" _
    , Destination:=Range(Cells(curRow + 1, curCol).Address))
    .Name = "990 Finder_284"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = False
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingAll
    .WebTables = """MainContent_GridView1"""
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With

Here is a cheat sheet for how RC and A1 addressing works: