Cycling through find results in a loop

rbsam

New Member
Joined
Jul 12, 2019
Messages
42
7IvMET1.png


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:

Code:
[/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]
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Why not just use either Autofilter, or advanced filter?
 
Upvote 0
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Change the filter criteria.
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top