0

There have been a few posts about this, but cannot find anything relating specifically to my problem.

I want to have a WHERE search on a named range within VBA where the range is changeable e.g not simply A1:A10, but A1:End

I have tried the below, but only the last cell within the range is used in the SQL WHERE results

Set Brokers = Sheets("Brokers").Range("A2").End(xlDown)
Dim r As Variant
r = Brokers.Value

....    

WHERE c_broker = '" & r & "'"

I have also tried the below, but am getting a "type mismatch" error

WHERE c_broker IN( '" & Join(Application.Transpose(Sheets("Brokers").Range("A2").End(xlDown).Value), "','") & "')"

If anyone could help - or let me know if not possible at all - that would be great!

Thanks!

EDIT: To further explain this, I have a range in sheet "Brokers" starting from A2 going down where the end can change depending on user input e.g it can be A2:A10 or A2:A15. I want SQL to pick up on this range at whatever it currently is, which is why I've been trying to use End(xlDown) in the VBA. I have tried changing the above to the below, but am having an "Application-defined or object-defined error"....the same goes when I add this in the SQL WHERE line - unless I am doing something incorrect with the below?

Set Brokers = Sheets("Brokers").Range("A2", Range("A2").End(xlDown))
PeterK
  • 99
  • 6
  • End(xlDown) of A2 is A2 https://learn.microsoft.com/en-us/office/vba/api/excel.range.end – Serg Nov 23 '20 at 07:19
  • First, your SQL query needs to be in quotes, e.g. `sSQL = "WHERE...` but maybe that is just how you pose the question. Then `WHERE` takes a single argument, not a range. For a range, you need to execute the SQL once for each value in the range. – Paul Ogilvie Nov 23 '20 at 07:25
  • @PaulOgilvie - sorry, was just showing part of the code the " is there. For your second part, when I run `WHERE c_broker IN( '" & Join(Application.Transpose(Sheets("Brokers").Range("A2:A15").Value), "','") & "')"` this works for all values within the range? So it isn't possible if there is a changeable end in the range? – PeterK Nov 23 '20 at 07:29
  • @Serg - just tried `Set Brokers = Sheets("Brokers").Range("A2", Range("A2").End(xlDown))` but am getting an "application-defined or object-defined error" – PeterK Nov 23 '20 at 07:33
  • Please update the question. Explain how the range should be constructed. – Serg Nov 23 '20 at 07:37
  • Avoid the use of `xlDown`. Find last row as shown [Here](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920) and then use that in your query. Once you find the last row then your range becomes `Sheets("Brokers").Range("A1:A" & Lastrow)` – Siddharth Rout Nov 23 '20 at 07:44
  • Still it's not clear which way user input defines the range. Is it a range of cells having values terminated with an empty cell, for example? Is a named range an option? See https://learn.microsoft.com/en-us/office/troubleshoot/excel/create-dynamic-defined-range – Serg Nov 23 '20 at 07:55
  • @SiddharthRout - I have just tried this but am getting the "Application-defined or object-defined error" on the SQL part when I put `c_broker IN( '" & Join(Application.Transpose(Sheets("Brokers").Range("A2:A & lrow").Value), "','") & "')"` after doing `lrow = Sheets("Brokers").Range("E5").End(xlDown).Row` beforehand – PeterK Nov 23 '20 at 08:00
  • @Serg - the SQL produces the table based on the range in the brokers sheet, which can vary depending if the user only inputs 1 or 1000 entries in the brokers sheet in column A – PeterK Nov 23 '20 at 08:02
  • There are two parts in your question. **1.** Constructing your excel range **2.** Constructing your SQL query. I just showed you how to construct your range (*BTW you still ignored my suggestion of `xlDown`*). Once you have your range correct, you need to work on your sql query. – Siddharth Rout Nov 23 '20 at 08:07
  • Looks like https://learn.microsoft.com/en-us/office/troubleshoot/excel/create-dynamic-defined-range is an option. – Serg Nov 23 '20 at 08:09
  • @SiddharthRout - thank you for your help....I have done the following `With Sheets("Brokers") .Range(.Range("A2"), .Range("A2").End(xlUp)).Name = "Brokers" End With` but when I check Name Manager it is only selecting cells A1:A2? Think once I've cracked this, then the SQL will fall into place........I hope!! – PeterK Nov 23 '20 at 08:39
  • Ok, so now I have used `Sheets("Brokers").Range("A2:A" & Range("A2").End(xlDown).Row).Name = "DataTable"` and this works for the range (sorry @SiddharthRout about the xlDown!)......now the issue is adding it to the SQL as have tried `WHERE c_broker = '" & DataTable & "'"` but this doesn't pick up anything – PeterK Nov 23 '20 at 09:08
  • That clearly means that you did not read the link that I gave you. This is what was needed... `LastRow = Sheets("Brokers").Range("A" & Sheets("Brokers").Rows.Count).End(xlUp).Row` and then `Sheets("Brokers").Range("A2:A" & LastRow)` – Siddharth Rout Nov 23 '20 at 16:55
  • @SiddharthRout thank you for your help.....I have incorporated this in now, but how does this get linked to the SQL query? – PeterK Nov 24 '20 at 02:31
  • First hardcode and test a sample working query. Once you have that post that query here. We will then simple replace the relevant parts of that query. – Siddharth Rout Nov 24 '20 at 05:27

0 Answers0