Cycling through find results in a loop

rbsam

New Member
Joined
Jul 12, 2019
Messages
42


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]
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,424
Office Version
365
Platform
Windows
Why not just use either Autofilter, or advanced filter?
 

rbsam

New Member
Joined
Jul 12, 2019
Messages
42
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

New Member
Joined
Jul 12, 2019
Messages
42
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,424
Office Version
365
Platform
Windows
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

New Member
Joined
Jul 12, 2019
Messages
42
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,424
Office Version
365
Platform
Windows
Change the filter criteria.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,948
Messages
5,483,839
Members
407,415
Latest member
Anton1999

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top