Search cell range for value and return another cell value in same column

Hi All,

Fairly new to VBA and I have a regression test matrix in Excel with Business Processes mapped against Test Cases which perform the regression against that business process. I want to overlay a VBA form over the Excel sheet which will allow any user to filter down by the Level 1 - Level 3 process and it returns all the tests applicable to that business process. (Please see the example regression test matrix below).

Example Regression Test Matrix:
test_matrix.png


Step 1: The User filters by the Level 1 Process
test_matrix_filtered_L1.png

Step 2: The User filters by the Level 2 process
test_matrix_filtered_L2.png

Step 3: The User filters by the Level 3 Process
test_matrix_filtered_L3.png

Step 4: The User identifies all test cases with an 'X'
test_matrix_filtered_findX.png

So the VBA I need help with is (and the process I think would work):

1. Searching for all cells with an 'X' text value on the filtered sheet (so visible cell range)
2. When an 'X' has been found, get and store the cell address of the 'X' (e.g. $D$2)
3. Change the stored cell address's row number to 'Row 1' (e.g $D$1 - as that is where the test case name will be stored)
4. Search for the new cell address and return the cell's text value (the Test Case name e.g. "Test 1")
5. Add the Cell value to a listbox on a VBA form
6. Find the next 'X' in the range

Any help will be much appreciated!

It would be easier to help and test a possible solution if you could attach a copy of your file that includes the form with the list box.

Try the attached file. Just click the "Show Userform" button.

My pleasure.