Cycling through find results in a loop

[rbsam]

I'm trying to set something up so a user can make their criteria selection via data validation lists. In my example, their choice is Car, Red, UK (vehicle, colour, country). Clicking this searches through rows in column A, D and E, it then returns the corresponding row's B value into J3 and the corresponding row's C column into I3.

I tried doing this with Match but apparently you can't use that to cycle through results, only to find 1 match.

I found the following code and adapted it to my data:

[/FONT][/COLOR]

    Dim lLoop As Long
    Dim rFoundCell As Range
    
    choice = Range("H6").Value
    
    With Range("A:A")
       Set rFoundCell = .Cells(1, 1)
            For lLoop = 1 To WorksheetFunction.CountIf(.Cells, choice)
            
            Set rFoundCell = .find(What:=choice, After:=rFoundCell, _
                LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, MatchCase:=False)
            
                MsgBox rFoundCell
            Next lLoop
    End With
[FONT=Arial, Helvetica Neue, Helvetica, sans-serif][COLOR=#242729]

[FONT=Arial, Helvetica Neue, Helvetica, sans-serif]This prompts a MsgBox to appear 3 times because the value of H6 is "Car" and "Car" appears 3 times in column A.[/FONT]

[FONT=Arial, Helvetica Neue, Helvetica, sans-serif]My question is:
a) How can I adapt this to have 3 conditions, so rather than just looking for the value of H6, it has to find a row that contains H6, I6 and J6
b) Once it has found rows that match those 3 conditions, I need it to execute the found value once every click. This is because once it has found the row value, I'll add my own "B" and "C" to that row so that I can have the value of "B6" for example (if one of the finds return row 6) into I3/J3. But rather than it listing all of the results in one go, I want it to show a different result on every click (replacing the last)

Hope this makes sense, any help would be much appreciated![/FONT]

 

[Fluff]

Why not just use either Autofilter, or advanced filter?

 

[rbsam]

Why not just use either Autofilter, or advanced filter?

I've never used them before, just reading up on them now. But is there a way to have it so clicking the button will find one match, clicking it a second time then replaces the previous find with another, and so on etc.

 

[rbsam]

The only way that comes to mind to do what I described above, and I don't know if this would work, but the logic that comes to mind is:
1. The filter returns a list/array (not sure of the terminology?) of matching results
2. These results are indexed (5 results returns the number 5)
3. A loop is made so upon each click it will return the next index position result

No idea how you make a loop only iterate upon a click though

EDIT: Regarding point 3, I wonder if you could have it so the result that was printed is assigned to a variable like current_result, and then each button click has an if statement which is if value = current_result then +1 on the loop index position

 

[Fluff]

With a filter you filter the three columns, so it shows you every row that contains all 3 values.
You can then modify the data as needed.

 

[rbsam]

With a filter you filter the three columns, so it shows you every row that contains all 3 values.
You can then modify the data as needed.

My question is more around how you would get the button to present a different filtered result on each click

 

[Fluff]

Change the filter criteria.