Find a string and clear cell's contents to the right

[Johnatha]

Hi everyone,

I have successfully created a macro that searches for a specific string (for example, "EEOG"), and clears the contents of the cell 3 columns to the right of said string. My question is- how can I get my code to find ALL of said strings, and clear the contents of the cell 3 columns to the right, throughout my entire workbook? Right now it only finds 1 row that contains "EEOG" but not all the other rows.

Here is my working code:

Sub Macro1()

    Cells.Find(What:="EEOG", LookIn:=xlFormulas, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False).Offset(, 3).Select
    Selection.ClearContents

End Sub

Thanks
-Johnathan

[Johnatha]

Solved! Ended up using :

Dim xRange As Range 
Dim xCel As Range
   Set xRange = Range("A:A")
    For Each xCel In xRange
        If xCel.Value = "EEOG" Then
            xCel.Offset(, 3).ClearContents
        End If
    Next xCel

[mancubus]

reading:
https://msdn.microsoft.com/en-us/lib.../ff839746.aspx

and adopting:

Sub vbax_54785_Find_All_Occurences_Of_SearchString()


    Dim FoundCell As Range
    Dim FirstAddress As String
    
    With ActiveSheet.UsedRange.Cells
        Set FoundCell = .Find(What:="EEOG", LookIn:=xlFormulas, MatchCase:=True)
        If Not FoundCell Is Nothing Then
            FirstAddress = FoundCell.Address
            Do
                FoundCell.Offset(, 3).ClearContents
                Set FoundCell = .FindNext(FoundCell)
            Loop While Not FoundCell Is Nothing And FoundCell.Address <> FirstAddress
        End If
    End With


End Sub

[jolivanes]

Without looping.
Might be faster. Amount of data dependent.

Sub test()
Application.ScreenUpdating = False
    Rows(1).Insert
    With Columns("A")
        .AutoFilter 1, "EEOG"
        .Offset(, 3).ClearContents
        .AutoFilter
    End With
    Rows(1).Delete
Application.ScreenUpdating = True
End Sub

[snb]

Even simpler/faster

Sub M_snb()
   [D1:D2000]=[if(A1:A2000="","",if(A1:A2000="EEOG","",D1:D2000))]
End sub