VBA code to run a query where criteria equals a range of data

[jeff_cp]

I have a spreadsheet that I need to run a query on but only if a field is in a range of data. I want to paste in a list of stock numbers in a worksheet (starting in cell A2 going down) but only have the query return data for those stock numbers. The number of stock numbers will vary and others will be using this spreadsheet so I can't rely on them to create a named range if that's necessary.

What I envision is pasting in the stock numbers in one worksheet and hitting a refresh button that will refresh the query on another worksheet based on the stock numbers I pasted in.

Is this possible?

 

[COwen]

You can use a For/Next loop...

 

[COwen]

Here is the code for a For/Next loop. Assuming that your numbers are on Sheet1 and the selection you want to paste is Sheet2

Sub Refresh()
Dim Ref

For Ref = 1 To 1000
If Sheet1.Cells(Ref, 1).Value <> "" Then

Sheet2.Cells(Ref, 1).Value = Sheet1.Cells(Ref, 1).Value

End If
Next Ref
End Sub

 

[Fazza]

Queries can use the worksheet name. Say the worksheet is named "YourSheetName", then use [YourSheetName$]
Problem with this is if the worksheet name changes.

Using VBA can overcome this. To either edit the SQL to use worksheet name at the time.
Or via VBA use a normal named range reference that is updated at the time. Maybe like this,
range("A1").currentregion.resize(,1).name = "YourTableName"

I guess another approach altogether - without VBA - could be to use a defined name for the whole column and then in the SQL exclude Null values.

 

[jeff_cp]

I actually prefer to do this with a SQL query but couldn't figure out how.

 

[Fazza]

I understood you had an SQL query, and the question was about making sure the correct range was referenced. Is that good now?

SQL will be specific to your set up. Sounds like you have something like,

SELECT A.fields
FROM maintable A, filterlist B
WHERE A.stockcode = B.stockcode

which is just another way to write what you may also see/have,
SELECT A.fields
FROM maintable A INNER JOIN filterlist B ON A.stockcode = B.stockcode

If you're after further help, please advice specific question.