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

777nycole

New Member
Joined
Feb 24, 2025
Messages
1
Office Version
  1. 2024
  2. 2021
Platform
  1. Windows
1740380324394.png

VBA Code:
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
 
Last edited by a moderator:
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 )
 
Upvote 0
Maybe try this:
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,226,895
Messages
6,193,549
Members
453,807
Latest member
PKruger

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top