Excel FindNext function

Question

Find Next question for Stack Overflow

I have what's turning into a long VBA script. The objective is to let the user type in all or part of a last name and first name, then edit the record. The problem is that the data may have duplicate search keys. For example, John Smith's key ID would be SmithJohn. Suppose there is also a Ray Smith in the table (ID = SmithRay). If the user searches for Smith, John Smith will get the hit. If the user actually wants Ray Smith, I've given them a button to go to the next page of my three-page form and search again. I've been trying to use FindNext() as described in http://msdn.microsoft.com/en-us/library/office/aa195732(v=office.11).aspx -- and it's not working.

My script creates a number of global variables. including these:

Dim cPersonID As String
Dim lRow As Long
Dim lPart As Long
Dim nLastRow As Long, i As Long
Dim strSearch As String
Dim aCell As Range , bCell As Range
Dim ws As Worksheet

Continuing my example, the user types Smith in the last name text box. Therefore, cPerson ID will have the value "Smith" lRow will have the row number for this record, nLastRow will have the value of the last non-blank row. My search expression (in the procedure btnNameFindP1_Click()) looks like this:

Set aCell = ws.Range("A1:A" & nLastRow).Find(What:=strSearch, _
    LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
    SearchDirection:=xlNext, MatchCase:=False)

For reasons I don't understand, aCell ends up containing the full value of the search string (in this case SmithJohn). strSearch also ends up with that value. But cPersonID only contains the search string (Smith). I can live with that.

If the user clicks a button to search for the next matching field, the script shifts the focus to Page2 and attempts to search for the next occurrence. Here's the script:

Private Sub btnNameFindNextP1_Click()

frmLWVdataEditFindTwoPage.Value = 1
Set ws = Worksheets("8_4MemDB_NHS")

'Find next matching member in table
Set aCell = ws.Range("A1:A" & nLastRow).FindNext()

I've tried many variants of FindNext(). I suspect I may not be handling the transition between Page1 and Page2 of the multi-screen form. But at this point I'll try any suggestions. After three hours of trial-and-error I've run out of ideas.

Thanks, Tony Lima

Answer 1

range.FindNext() neturns first cell which matches the condition. If you want to get the next result, you should specify the argument of findnext which specify the position to be searched after.

For example: Assume A3,A9 and A23 cells contain "Smith" in the range("A1:A1000"), and their contents are "SmithJohn","SmithRay","SmithAdams" respectively.

set aRange = range("A1:A1000);

firstHit = aRange.Find(What:="Smith", _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False);
msgbox("FirstHit is ",firstHit.value);

secondHit = aRange.FindNext(firstHit);
msgbox("SecondHit is ",firstHit.value);

thirdHit = aRange.FindNext(secondHit);
msgbox("ThirdHit is ",firstHit.value);

On execution, 　cell A3 is set to firstHit and the message "FirstHit is SmithJohn" will appear,

then cell A9 is set to secondHit and the message "SecondHit is SmithRay" will appear,

and then cell A23 is set to thirdHit and the messega "ThirdHit is SmithAdams" will appera.

I hope, this example gives you some idea of usages of 'find' and 'findnext'.