Remove hard-coding from macro/SQL

[shre0047]

Currently, I have a macro report to pull tests from a database which is hard-coded and would like it so the end user can change the values on the tests in Sheet1 and the formula will change.

Example:
Sheet1:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Test #1[/TD]
[TD]4846[/TD]
[/TR]
[TR]
[TD]Test #2[/TD]
[TD]65489[/TD]
[/TR]
[TR]
[TD]Test #3[/TD]
[TD]1289[/TD]
[/TR]
</tbody>[/TABLE]


Within the macro where it extracts it from the database via SQL query, I have it hard-coded but would like to change it so it pulls it via the values of column B in 'Sheet1'

SQLquery = SQLquery & " Where t.TS_TEST_ID = '[B]4846[/B]' OR t.TS_TEST_ID = '[B]65489[/B]' OR t.TS_TEST_ID = '[B]1289[/B]' "

 

[bs0d]

Currently, I have a macro report to pull tests from a database which is hard-coded and would like it so the end user can change the values on the tests in Sheet1 and the formula will change.

Example:
Sheet1:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Test #1[/TD]
[TD]4846[/TD]
[/TR]
[TR]
[TD]Test #2[/TD]
[TD]65489[/TD]
[/TR]
[TR]
[TD]Test #3[/TD]
[TD]1289[/TD]
[/TR]
</tbody>[/TABLE]


Within the macro where it extracts it from the database via SQL query, I have it hard-coded but would like to change it so it pulls it via the values of column B in 'Sheet1'

SQLquery = SQLquery & " Where t.TS_TEST_ID = '[B]4846[/B]' OR t.TS_TEST_ID = '[B]65489[/B]' OR t.TS_TEST_ID = '[B]1289[/B]' "

Try:

Dim Test1 as Long
Dim Test2 as Long
Dim Test3 as Long
Test1 = Sheets("Sheet1").Range("B2").value
Test2 = Sheets("Sheet1").Range("B3").value
Test3 = Sheets("Sheet1").Range("B4").value

SQLquery = SQLquery & " Where t.TS_TEST_ID = '[B]" & Test1 & "[/B]' OR t.TS_TEST_ID = '[B]" & Test2 & "[/B]' OR t.TS_TEST_ID = '[B]" & Test3 & "[/B]' "

 

[shre0047]

This worked perfectly and also works when B4 or any of the cells doesn't have a value. Thank you!

 

[bs0d]

Happy to help.