Results 1 to 7 of 7

Thread: Cycling through find results in a loop
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Cycling through find results in a loop



    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:
        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


    This prompts a MsgBox to appear 3 times because the value of H6 is "Car" and "Car" appears 3 times in column A.

    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!

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Cycling through find results in a loop

    Why not just use either Autofilter, or advanced filter?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cycling through find results in a loop

    Quote Originally Posted by Fluff View Post
    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.

  4. #4
    New Member
    Join Date
    Jul 2019
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cycling through find results in a loop

    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 by rbsam; Jul 22nd, 2019 at 08:50 AM.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Cycling through find results in a loop

    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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    Jul 2019
    Posts
    42
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Cycling through find results in a loop

    Quote Originally Posted by Fluff View Post
    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

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,958
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Cycling through find results in a loop

    Change the filter criteria.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •