Solved: FindNext

[Opv]

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]

[lucas]

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]

[Opv]

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

[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.....

[lucas]

don't use activate then. highligh the cell...

[Opv]

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

[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

[Opv]

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

[lucas]

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?

[lucas]

Remember Opv, the quickest way to a solution is asking the right question.

We'll get this sorted.

[Opv]

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.

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.

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?

[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.

[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]

[Opv]

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.

[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.

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

[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]

[Opv]

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

[Opv]

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.