I need help with making a macro relative to a selection field within the macro

[namer98]

Hello, 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\990 Finder.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!

 

[DeBeuz]

Hi,

Try replacing "$C$576" with something like ActiveCell.Offset(0, -1).Address
The example should return the addres of the cell left (-1) of the active cell, but on the same (0) row.

Does this help?

Paul

 

[namer98]

It did help! Thank you so much. I am sorry for taking so long to get back to you, but I just had my first baby.

If you don't mind, I have another question.

The web query is a variable one. So I click B1 (which has the data for the variable), run the macro, it then asks me to give it the data, which is of course B1, and then puts the query into C1 (instead of the same constant cell, the active offset being (0,1)). I am sure this can be done, but I don't know how. How do I make it such that the web query uses the cell I clicked on for the variable web query?

Thank you again for the help!

 

[DeBeuz]

Hi,

Congratulations! Your first born is always special.

I do not quite get your question.
Also I'm not familiar with your query.
If the query is in the cell you clicked, you should be able to pick it up with ActiveCell.Value.
How to put it into the query, I have no idea.
Maybe you should change someting in the '990 Finder.iqy' too?

Succes,

Paul

 

[namer98]

Thanks!

I am new to web queries, but this one has a parameter in it. The query will insert the data into cell Cx based on information contained in cell Bx. With the macro as it currently runs, I click cell Bx, run the query, and it then asks me for the parameters, which is cell Bx. But at least now, with the active offset, it puts the data into cell Cx. I want to have the macro take the information from the cell I click (Bx) instead of it asking me to input a cell value for it to take the data for the query. I hope I make sense as I am learning this on the fly.

Thanks so much!

 

[DeBeuz]

I still think you will need to modify the 990 Finder.iqy to allow for the parameter to be used.
Maybe there is already a name that is being asked for to put the query in.

On this forum I searched for 'querytable input' and found some pieces you can try out on a safe copy.

        .Parameters.Add ActiveCell, xlRange        'Was Sheets("Sheet1").Range("Z1"), xlRange
        .Refresh BackgroundQuery:=False
        .Refresh

and a named parameter 'ProductID'

        .Parameters.Add("ProductID", xlParamTypeVarChar)
        .SetParam xlRange, ActiveCell       'Was Sheets("Sheet1").Range("Z1")
        .RefreshOnChange = True