Excel find first cell string then find the second cell string and return address of the second cell

[777nycole]

Sub test()
Dim c As Range, FoundCells As Range
Dim c1 As Range
Dim firstaddress As String

Application.ScreenUpdating = False
With Sheets("Sheet1")
    'find first cell that contains "rec"
    Set c = .Cells.Find(What:="rec", After:=.Cells(Rows.Count, 1), LookIn:=xlValues, LookAt:= _
    xlPart, MatchCase:=False)
   
    'if the search returns a cell
    If Not c Is Nothing Then
        'note the address of first cell found
        firstaddress = c.Address
        Set c1 = Cells(c.Row + 0, c.Column - 1).Address
        Do
            'FoundCells is the variable that will refer to all of the
            'cells that are returned in the search
            If FoundCells Is Nothing Then
                Set FoundCells = c
            Else
                Set FoundCells = Union(c, FoundCells)
            End If
            'find the next instance of "rec"
            Set c = .Cells.FindNext(c)
        Loop While Not c Is Nothing And firstaddress <> c.Address
               
        'after entire sheet searched, select all found cells
        MsgBox FoundCells.Address
    Else
        'if no cells were found in search, display msg
        MsgBox "No cells found."
    End If
End With
Application.ScreenUpdating = True

End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes the code much easier to read/debug & copy. My signature block at the bottom of this post has more details. I have added the tags for you this time.

This code appears to be pretty much straight from here.
What is your question?
What does this code do or not do in terms of what you want?

(Also note that I deleted your post from that other thread per #12 of the Forum Rules )

 

[bebo021999]

Maybe try this:

Option Explicit
Sub FindCells()
    Dim ws As Worksheet, cell As Range
    Dim foundCells As String, firstAddress As String
    
    ' choose sheet to find
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change to actual sheet name
    
    ' Find "rec"
    With ws.UsedRange
        Set cell = .Find(What:="rec", LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
        If Not cell Is Nothing Then
            firstAddress = cell.Address
            Do
                ' Store address found
                foundCells = foundCells & cell.Address & vbCrLf
                Set cell = .FindNext(cell)
            Loop While Not cell Is Nothing And cell.Address <> firstAddress
        End If
    End With
    
    If foundCells <> "" Then
        MsgBox "Cells with 'rec':" & vbCrLf & foundCells
    Else
        MsgBox "Not found cells with 'rec'."
    End If
End Sub