Need a VBA Solution - Clear contents of 7 cells to the right of cell

Question

I have a document where I need to look for a specific text and clear all text in the cells to the right of it. I don't want to delete the cells just clear them out. There are a total of 7 cells next to the original cell. I'm having a tough time getting this to work for more than one cell. Any help is much appreciated.

Here is My Code Right now:

Sub ClearCellNextToTextValue()
    Dim ws As Worksheet
    Dim cell As Range
    Dim searchRange As Range
    Dim foundCell As Range

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ' Replace "A1:A100" with the range where you want to search for the text value
    Set searchRange = ws.Cells
        
    Set foundCell = searchRange.Find(What:="TOTALS - Current Month", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True, SearchFormat:=False)

    If Not foundCell Is Nothing Then
        ' Loop through each found cell
        For Each cell In searchRange
            If cell.Address = foundCell.Address Then
                ' Clear the contents of the cell one column to the right of the found text
                cell.Offset(0, 1).ClearContents
                cell.Offset(0, 2).ClearContents
                cell.Offset(0, 3).ClearContents
                cell.Offset(0, 4).ClearContents
                cell.Offset(0, 5).ClearContents
                cell.Offset(0, 6).ClearContents
                cell.Offset(0, 7).ClearContents
            End If
        Next cell
    End If
End Sub

Answer 1

Please try this:

Sub ClearCellNextToTextValue()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range
    Dim firstAddress As String

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Consider setting this to a smaller range
    Set searchRange = ws.Cells

    Set foundCell = searchRange.Find("TOTALS - Current Month")
    
    'store the address of the first find result
    firstAddress = foundCell.Address
    
    Do While Not foundCell Is Nothing
        'Clear the 7 cells to the right
        foundCell.Offset(0, 1).Resize(1, 7).ClearContents
        
        ' Find the next occurrence
        Set foundCell = searchRange.FindNext(foundCell)
        
        ' FindNext loops back to the first found item. If so, we're done...
        If foundCell.Address = firstAddress Then Exit Do
        
    Loop
End Sub

The trick with using Find is that you need to use FindNext to continue the search past the first result.

Since FindNext loops back to the first found result, you also have to have a way to exit when that happens. This is done by storing the address of the first result and checking each subsequent found cell against that stored address.