VBA with ~1400 IDs

[shre0047]

I currently have a macro to extract ALMQC data from 25 IDs, but I'm wondering if there's a more efficient way to extract the data for 1400 IDs.

'Parameters
Dim Test1 As Long
Dim Test2 As Long
Dim Test3 As Long
Dim Test4 As Long
Dim Test5 As Long
Dim Test6 As Long
Dim Test7 As Long
Dim Test8 As Long
Dim Test9 As Long
Dim Test10 As Long
Dim Test11 As Long
Dim Test12 As Long
Dim Test13 As Long
Dim Test14 As Long
Dim Test15 As Long
Dim Test16 As Long
Dim Test17 As Long
Dim Test18 As Long
Dim Test19 As Long
Dim Test20 As Long
Dim Test21 As Long
Dim Test22 As Long
Dim Test23 As Long
Dim Test24 As Long
Dim Test25 As Long
Test1 = Sheets("Parameters").Range("B2").Value
Test2 = Sheets("Parameters").Range("B3").Value
Test3 = Sheets("Parameters").Range("B4").Value
Test4 = Sheets("Parameters").Range("B5").Value
Test5 = Sheets("Parameters").Range("B6").Value
Test6 = Sheets("Parameters").Range("B7").Value
Test7 = Sheets("Parameters").Range("B8").Value
Test8 = Sheets("Parameters").Range("B9").Value
Test9 = Sheets("Parameters").Range("B10").Value
Test10 = Sheets("Parameters").Range("B11").Value
Test11 = Sheets("Parameters").Range("B12").Value
Test12 = Sheets("Parameters").Range("B13").Value
Test13 = Sheets("Parameters").Range("B14").Value
Test14 = Sheets("Parameters").Range("B15").Value
Test15 = Sheets("Parameters").Range("B16").Value
Test16 = Sheets("Parameters").Range("B17").Value
Test17 = Sheets("Parameters").Range("B18").Value
Test18 = Sheets("Parameters").Range("B19").Value
Test19 = Sheets("Parameters").Range("B20").Value
Test20 = Sheets("Parameters").Range("B21").Value
Test21 = Sheets("Parameters").Range("B22").Value
Test22 = Sheets("Parameters").Range("B23").Value
Test23 = Sheets("Parameters").Range("B24").Value
Test24 = Sheets("Parameters").Range("B25").Value
Test25 = Sheets("Parameters").Range("B26").Value


SQLquery = SQLquery & " Where t.TS_TEST_ID = '" & Test1 & "' OR t.TS_TEST_ID = '" & Test2 & "' OR t.TS_TEST_ID = '" & Test3 & "' OR t.TS_TEST_ID = '" & Test4 & "' OR t.TS_TEST_ID = '" & Test5 & "' OR t.TS_TEST_ID = '" & Test6 & "' OR t.TS_TEST_ID = '" & Test7 & "' OR t.TS_TEST_ID = '" & Test8 & "' OR t.TS_TEST_ID = '" & Test9 & "' OR t.TS_TEST_ID = '" & Test10 & "' OR t.TS_TEST_ID = '" & Test11 & "' OR t.TS_TEST_ID = '" & Test12 & "' OR t.TS_TEST_ID = '" & Test13 & "' OR t.TS_TEST_ID = '" & Test14 & "' OR t.TS_TEST_ID = '" & Test15 & "' OR t.TS_TEST_ID = '" & Test16 & "' OR t.TS_TEST_ID = '" & Test17 & "' OR t.TS_TEST_ID = '" & Test18 & "' OR t.TS_TEST_ID = '" & Test19 & "' OR t.TS_TEST_ID = '" & Test20 & "' OR t.TS_TEST_ID = '" & Test21 & "' OR t.TS_TEST_ID = '" & Test22 & "' OR t.TS_TEST_ID = '" & Test23 & "' OR t.TS_TEST_ID = '" & Test24 & "' OR t.TS_TEST_ID = '" & Test25 & "' "

 

[mse330]

Hi shre0047,

If this is SQL query, why don't you use IN () instead of all the ORs ? Check below code if it does what you want ... Results will appear in the immediate window

Sub test()

Dim ws As Worksheet, Arr, Str$
Set ws = Sheets("Parameters")
Arr = ws.Range("B2", ws.Range("B" & Rows.Count).End(xlUp)).Value

For x = LBound(Arr) To UBound(Arr)
   Str = IIf(Len(Str) = 0, "'" & Arr(x, 1) & "'", Str & ", " & "'" & Arr(x, 1) & "'")
Next

SQLquery = SQLquery & " Where t.TS_TEST_ID IN (" & Str & ")"

Debug.Print SQLquery

End Sub