Update the hard-coded line

[shre0047]

I have a VBA to pull data from a database, but I am trying to not hard-code it. I am needing to pull a few tests from the database and currently running it, then changing the code to update the ids.

Currently:

SQLquery = SQLquery & " Where t.TS_TEST_ID = '39324' OR t.TS_TEST_ID = '39166' OR t.TS_TEST_ID = '12345'

What I want to do is the line is looking at cell values in Sheet1.

Script#1	39324
Script#2	39166
Script#3	12345
Script#4	32456

<tbody>
</tbody>

I want to extract 20-25 at a time so the table listed above would provide all the scripts. This is what I have so far:

SQLquery = SQLquery & " AND t.TS_TEST_ID = " & "'" & Sheets("Sheet1").Range("B1").Value & "'" & "

 

[Joe4]

It is much easier in you take advantage of the IN operator.
See: https://www.w3schools.com/sql/sql_in.asp

So, if we wanted to loop through all the values in column B on Sheet1 to build our list of IDs and build our WHERE condition, it would look something like this:

    Dim lr As Long
    Dim r As Long
    Dim myList As String
    Dim cond As String
    
'   Find last row in Sheet1, column B
    lr = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
    
'   Loop through all values in column B and make them a list
    For r = 1 To lr
        myList = myList & "'" & Sheets("Sheet1").Cells(r, "B") & "',"
    Next r
    
'   Drop last comma off of myList
    If Len(myList) > 0 Then
        myList = Left(myList, Len(myList) - 1)
'       Build condition
        cond = " WHERE t.TS_TEST_ID IN (" & myList & ")"
'       Return condition for inspection
        MsgBox cond
    End If