Find and FindNext for Excel VBA

Question

I've been stuck trying to figure out what to do with this, but basically I want a way to print out the value in column B given a specific value that matches column A. So for example:

Column A    Column B
1           ABC
2           DEF
3           GHI
1           JKL

I want to, after using find/findnext or whatever it is, to print out this string:

ABC JKL

I tried using

Set cellFound = ActiveWorkbook.Worksheets("sheet1").Range("F1:F1000000").Find("1")
string = cellFound.Offset(0, 1).value

And I have a loop to loop through as many time as it needs to get all the rows taken care of. But with find it keeps returning me the same first string ("ABC") and the string ends up being ABC ABC instead of ABC JKL

I tried using FindNext instead of find, but what I got is a 1004 Error. So I'm not really sure where I'm doing this wrong. Anyone has any idea?

Answer 1

You don't need FindNext if you start each Find after the previous one:

Sub qwerty()
   Dim rFirst As Range, r As Range
   Dim A As Range
   Set A = Range("A:A")
   Do
      If rFirst Is Nothing Then
         Set rFirst = A.Find(What:=1, After:=A(1))
         Set r = rFirst
      Else
         Set r = A.Find(What:=1, After:=r)
         If r.Address = rFirst.Address Then Exit Do
      End If
         MyString = MyString & " " & r.Offset(0, 1)
   Loop

   MsgBox MyString
End Sub

Answer 2

You need to call Find once, and then successively FindNext

Dim rng As Excel.Range
Set rng = ActiveWorkbook.Worksheets("sheet1").Range("F1:F1000000")
Set cellFound = rng.Find("1")
Do While Not cellFound Is Nothing
    Set cellFound = rng.FindNext
Loop

Reference:

• Find method
• FindNext method

Answer 3

When using the Range.FindNext method, one need just include some reference to the initial find position. For example, I recorded this macro using excel; while I'm not a fan of using selection and activate, I think it helps to understand how the method functions:

Sub Using_Find()
    Selection.Find(What:="my search string here", After:=ActiveCell _
        , LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Selection.FindNext(After:=ActiveCell).Activate
    Selection.FindNext(After:=ActiveCell).Activate
    Selection.FindNext(After:=ActiveCell).Activate
    Selection.FindNext(After:=ActiveCell).Activate
    Selection.FindNext(After:=ActiveCell).Activate
    Selection.FindNext(After:=ActiveCell).Activate
End Sub

To generate this subroutine, I used the record > macro in excel, then selected Home > Find & Select > Find.

The way I see this subroutine working is:

Step #1: Find the first location of the string, activate it;

Step #2: FindNext looks after the active cell that we just activated, finds the next location of the string, then activates it;

Etc. etc. So, the observation here is that the .FindNext method needs some reference to the prior find cell (which the first answer accomplishes by manually identifying it as a unique reference). I'm not saying anything to that answer, it works just as well. My goal was to help provide some insight into the Range.FindNext method.

Some other points worth mentioning:

Range.FindNext will return a Range object. (Microsoft)

The After parameter is described as:

"The cell after which you want to search. This corresponds to the position of the active cell when a search is done from the user interface. Be aware that After must be a single cell in the range. Remember that the search begins after this cell; the specified cell is not searched until the method wraps back around to this cell. If this argument is not specified, the search starts after the cell in the upper-left corner of the range." (Microsoft)

...and

Under the Remarks section, Microsoft notes that, "The search will wrap around to the beginning of the range." They suggest to save the first address and do a check against it for each subsequent .FindNext. This way, once the method does wrap around, it will check the address against the first and end the check.

So, modeling the Range.FindNext Method provided by Microsoft, I wrote this introductory subroutine for review:

Sub USING_FIND()
'this line sets the range to our used range on the active sheet
    With ActiveSheet.UsedRange
'setting c variable to .Find method, where the first value is what we're looking for,
'i.e. "1"; LookIn:= can be changed to our needs but set currently to xlValues
        Set c = .Find(1, LookIn:=xlValues)
'begin first conditional; this conditional checks c (our .Find method) to see if it has
'some reference, then sets the address to a constant 'firstAddress' so we can check it
'against the .FindNext returns later to prevent endless loop
        If Not c Is Nothing Then
            firstAddress = c.Address
'Do...is where we place our "work"; this can be a redirect to another function/sub, etc
'for now I've just tossed a msgbox as a placeholder that returns the offset 1 column over
            Do
                MsgBox c.Offset(0, 1)
'Now we set c to the .FindNext method, using the original .Find method as the 'after'
        Set c = .FindNext(c)
'Another empty reference check/exit as a conditional
    If c Is Nothing Then
        GoTo DoneFinding
'ends the empty reference conditional
    End If
'using our .FindNext method that we replaced 'c' with earlier, we can now loop through
'the remainder of the value returns.  The Loop While 'c.Address <> firstAddress' sentence
'is checking that each subsequent .FindNext address IS NOT the first address;
'-our loop will return to the 'Do' sentence to repeat the loop, starting on the
'MsgBox c.Offset(0,1) sentence with the next string occurence
'-the characters '<>' means 'does not equal'; i.e. the opposite of '='
    Loop While c.Address <> firstAddress
'this ends the address check loop
    End If
DoneFinding:
    End With
End Sub

To adjust this code to your specific needs, we can change the sentence after the Do line: 'MsgBox c.Offset(0,1)' to our specific needs.

Depending on how complex your output needs are, you can add all occurrences to an array, then have the array output the values in order of how you want to see them. This can be done by redim array and preserve each return. Once the .Find loop completes, open a new workbook with the Workbooks.Open method, and run a quick loop that takes each array value and places it in the order that you prefer.

Another option is to 'print' to .txt. Open a new .txt as #1, then 'print' accordingly. This can also be done as a second subroutine via the array option suggested previously.

Hope this helps add some context to your initial question with respect to the .FindNext method, as well as provides some ideas for future direction/implementation. Good luck!

Microsoft page on Range.FindNext Method: https://msdn.microsoft.com/en-us/VBA/Excel-VBA/articles/range-findnext-method-excel

Answer 4

    Function FindMultiResut(ByRef What As String, _
                            ByRef FindRng As Range, _
                            ByRef OutputRng As Range, _
                            ByRef Delimite As String)

        Dim fRng As Range
        Dim Rng1 As Range
        Dim Rng2 As Range
        Dim temp As String

        Set fRng = FindRng
        Do
            If Rng1 Is Nothing Then
                Set Rng1 = fRng.Find(What:=What, After:=fRng(1))
                Set Rng2 = Rng1
            Else
                Set Rng2 = fRng.Find(What:=What, After:=Rng2)
                If Rng2.Address = Rng1.Address Then Exit Do
            End If

            If OutputRng.Worksheet.Cells(Rng2.Row, OutputRng.Column) <> Empty Then
                temp = temp & OutputRng.Worksheet.Cells(Rng2.Row, OutputRng.Column) & Delimite
            End If
        Loop
        FindMultiResut = Left(temp, Len(temp) - 1)
    End Function

Answer 5

Here is an implementation of the suggestion I made in my comment under your question.

Function RowBeforeLast(ByVal What As Variant) As Long

Dim Fnd As Range

Set Fnd = Range("E:E").Find(What:=What, After:=Range("E1"), _
                            LookAt:=xlWhole, _
                            Searchdirection:=xlPrevious)
If Not Fnd Is Nothing Then
    Set Fnd = Range("E:E").Find(What:=What, After:=Fnd, _
                                LookAt:=xlWhole, _
                                Searchdirection:=xlPrevious)
    If Not Fnd Is Nothing Then RowBeforeLast = Fnd.Row
End If

End Function

It's designed as a UDF so that you can call it from the worksheet with a worksheet function like =RowBeforeLast(E5). You can also call it with code like

Private Sub TestGet()
    RowBeforeLast "GR 3"
End Sub

Either way it will return the row number in which the search criterium was found for the second time from the bottom of the column. If there is only one or no occurrance the function will return zero.