Consulting

Results 1 to 17 of 17

Thread: Solved: FindNext

  1. #1
    VBAX Expert
    Joined
    Feb 2010
    Posts
    684
    Location

    Solved: FindNext

    I would like to modify the script below to accommodate being able to find incrementally all instances that meet the search criteria. As I won't know up front which instance is the actual instance being sought, I need for the script to find the first instance, give me the option to select the found term OR click a "Find Next" option, etc., until the desired instance is found. I don't really care whether the script cycles me through the instances one at a time or whether it brings up a list of all found instances and allows me to select the desired instance, thereby taking me to the desired cell.

    Thanks,

    Opv

    [vba]
    Sub findEnglish()
    'Original author unknown

    Dim FindString As String
    Dim Rng As Range
    FindString = InputBox("Enter English Search term:")

    If Trim(FindString) <> "" Then
    With Worksheets("Dictionary").Range("A4:A5000")
    Set Rng = .Find(What:="*" & FindString & "*", _
    After:=.Cells(.Cells.Count), _
    LookIn:=xlValues, _
    LookAt:=xlWhole, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, _
    MatchCase:=False)
    If Not Rng Is Nothing Then Application.GoTo Rng, True

    ''''''''''''' MY CHANGES ''''''''''
    If ActiveCell.Column = 2 Then
    ActiveCell.Offset(0, -1).Select
    End If
    ''''''''''''' END MY CHANGES ''''''''

    End With
    End If
    End Sub

    [/vba]

  2. #2
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Read help on Do Until looping

    Here's an example since you didn't post your's for us to work with:

    [VBA]Option Explicit
    Sub GoToFnd()
    Dim c As Range, FirstFound As String
    Set c = Range("A:A").Find(what:=[C1], after:=[A1])
    FirstFound = c.Address
    c.Activate
    Do Until MsgBox("Look for next " & [C1], vbYesNo) = vbNo
    Set c = Range("A:A").Find(what:=[C1], after:=c)
    If c.Address = FirstFound Then
    MsgBox "No more " & [C1]
    Exit Sub
    End If
    c.Activate
    Loop
    End Sub[/VBA]
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  3. #3
    VBAX Expert
    Joined
    Feb 2010
    Posts
    684
    Location
    Quote Originally Posted by lucas
    Read help on Do Until looping

    Here's an example since you didn't post your's for us to work with:
    Thanks. I'll read up on Do Until loops. What should I have posted other than the current VBA script included in my original post?

    I like the initial input box produced by my current script. Not sure there is a way to keep it on the screen during the loop cycles. Multiple popup boxes can become annoying.

    Opv

  4. #4
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    just the workbook data that you are working with. So we don't have to create what you already have and maybe get it wrong.....
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  5. #5
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    don't use activate then. highligh the cell...
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  6. #6
    VBAX Expert
    Joined
    Feb 2010
    Posts
    684
    Location
    Quote Originally Posted by lucas
    just the workbook data that you are working with. So we don't have to create what you already have and maybe get it wrong.....
    OK. Here it is. Thanks,

    Opv

  7. #7
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Try this. I don't understand why you don't just filter the column though.

    You could just as easily use your inputbox for the input instead of range C1
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  8. #8
    VBAX Expert
    Joined
    Feb 2010
    Posts
    684
    Location
    Quote Originally Posted by lucas
    Try this. I don't understand why you don't just filter the column though.

    You could just as easily use your inputbox for the input instead of range C1
    Thanks. I wish I was higher on the learning curve so I could explain myself better. To be honest, I hadn't thought about the filter option. I am actually trying to do this for my brother, and he is the one that mentioned wanting a "search" function, so that's what got me on the path of exploring the Find and FindNext option.

    Anyway, the test file includes only a few sample terms for test purposes. The actual workbook will contain several thousand terms, some of which have the same string, i.e., "run" vs " to run" vs "run to" vs "prune, etc. That's why I included the wildcards in my search criteria. Anyway, what I have envisioned to this point is to be able to click my "Find English" button and search for the core string, "*run*" and be taken to the first instance. Then, if that is the term I intended, I want to be able to select and close the search box; otherwise, I would like to be able to click "Find Next" to proceed to the next instance, etc., until I get to the desired term.

    Opv

  9. #9
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    are you talking about a partial string?

    Yes it would help if you could lay out exactly what the project is and not just feed us bits at a time.

    I understand project creep but........you know.


    Anyway, what I have envisioned to this point is to be able to click my "Find English" button and search for the core string, "*run*" and be taken to the first instance. Then, if that is the term I intended, I want to be able to select and close the search box; otherwise, I would like to be able to click "Find Next" to proceed to the next instance, etc., until I get to the desired term.
    Is that not exactly what my example in post #2 does?
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  10. #10
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Remember Opv, the quickest way to a solution is asking the right question.

    We'll get this sorted.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  11. #11
    VBAX Expert
    Joined
    Feb 2010
    Posts
    684
    Location
    Quote Originally Posted by lucas
    are you talking about a partial string?
    Yes, that's why I included the wildcards in my Find routine to find any cell with the desired string as *searchstring* to find in case there are multiple instances with the desired string.

    Quote Originally Posted by lucas
    Yes it would help if you could lay out exactly what the project is and not just feed us bits at a time.

    I understand project creep but........you know.
    I Understand. Sorry I didn't think through the project more clearly before taking the plunge. The existing find subroutine has worked fine until my brother threw the curve ball at me about the multiple instances. It caused me to start exploring the FindNext option.

    The project is an ongoing English-Mvskoke dictionary that my brother is compiling as a learning and teaching tool. The English terms will be in Column A, the corresponding Mvskoke terms will be in Column B and the definitions will be in Column C. As I said earlier, I expect the database will eventually consist of several thousand terms.

    My brother is just wanting a way to find specific terms without having to go through the menus each time.

    Quote Originally Posted by lucas
    Is that not exactly what my example in post #2 does?
    Yes, I guess I was just confused or else did something wrong when I tested it the first time. Since you raised the notion of using a filter, I'm wondering if an automated advanced filter might not be a better alternative. I think I can set that up by myself.

    Which option do you think would be the most efficient and flexible?

  12. #12
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    try this. It uses wildcards and you can type in all small letters, it won't matter.

    try just typing in test in the inputbox.

    You can change it to highlight the entire row if you want.

    [vba]Option Explicit
    Option Compare Text
    Public Sub highlightRowsWithLabor()
    Const TEST_COLUMN As String = "A" '<=== change to suit
    Dim i As Long
    Dim iLastRow As Long
    Dim FindString As String
    ' FindString = Range("C1").Value
    FindString = InputBox("Enter English Search term:")
    With ActiveSheet
    'change next line to determine which column to find last row in.
    iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    ' iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
    For i = iLastRow To 1 Step -1
    ' If .Cells(i, TEST_COLUMN).Value Like "*test*" Then
    If .Cells(i, TEST_COLUMN).Value Like "*" & FindString & "*" Then
    ' Rows(i).Interior.ColorIndex = 6
    Cells(i, TEST_COLUMN).Interior.ColorIndex = 6
    End If
    Next i

    End With
    End Sub
    [/vba]
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  13. #13
    VBAX Expert
    Joined
    Feb 2010
    Posts
    684
    Location
    Quote Originally Posted by lucas
    try this. It uses wildcards and you can type in all small letters, it won't matter.

    try just typing in test in the inputbox.

    You can change it to highlight the entire row if you want.
    That is nice. Thanks. My only concern would be that the user would still have to manually scroll through potentially several thousand rows to find all the highlighted terms. I think my brother would be happier with either the find next or the advanced filter suggested earlier.

    Oh, I did note one apparent minor bug, if that's the correct term. If more than one search is performed, the original search term remain highlighted in addition to the new search term.

  14. #14
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Try this one. It allows you to search through using partial word searches in any case and you have the option to stop at any time or continue searching.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  15. #15
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    as far as fixing the bug in the one that highlights, just reset all cells in column a to no fill before you start each time:

    [vba]Public Sub highlightRowsWithLabor()
    Const TEST_COLUMN As String = "A" '<=== change to suit
    Dim i As Long
    Dim iLastRow As Long
    Dim FindString As String
    ' FindString = Range("C1").Value
    FindString = InputBox("Enter English Search term:")
    With ActiveSheet
    Columns("A:A").Interior.ColorIndex = xlNone
    'change next line to determine which column to find last row in.
    iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    ' iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
    For i = iLastRow To 1 Step -1
    ' If .Cells(i, TEST_COLUMN).Value Like "*test*" Then
    If .Cells(i, TEST_COLUMN).Value Like "*" & FindString & "*" Then
    ' Rows(i).Interior.ColorIndex = 6
    Cells(i, TEST_COLUMN).Interior.ColorIndex = 6
    End If
    Next i

    End With
    End Sub[/vba]
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  16. #16
    VBAX Expert
    Joined
    Feb 2010
    Posts
    684
    Location
    Quote Originally Posted by lucas
    Try this one. It allows you to search through using partial word searches in any case and you have the option to stop at any time or continue searching.
    Thanks. I think this option is what he's after. I will also mention the advance filter option to him. The only difference he would notice is that rather than clicking to go to the next instance, the script would filter out all by the matching instances.

    If it's OK, I'll leave this thread open while I continue to test.

    Thanks again for your patience and help.

    Opv

  17. #17
    VBAX Expert
    Joined
    Feb 2010
    Posts
    684
    Location
    Quote Originally Posted by lucas
    as far as fixing the bug in the one that highlights, just reset all cells in column a to no fill before you start each time:
    Thanks.

Posting Permissions

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