VBA Excel Find string in column and offset delete and repeat

Question

I have a working code to find a specific string in a column of a specific sheet, offset and clear the contents of a specific cell. However it only clears the first occurrence of this search and I would like to have the code work on all occurrences. Can someone help me to wrap a Loop or a FindNext around this code because I wasn't able to. Please see here below the code I already have. Thnx

Dim SearchValue6 As String 'located B9 
Dim Action6 As Range 'clear  
SearchValue6 = Workbooks.Open("C:\Users\.......xlsm").Worksheets("Sheet1").Range("B9").Value
    
On Error Resume Next

Worksheets(2).Columns("A:A").Select
Set Action6 = Selection.Find(What:=SearchValue6, After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False)

If Action6 Is Nothing Then
    'MsgBox "No clearings made in " & ActiveWorkbook.Name

Else
Action6.Activate
ActiveCell.Offset(0, 1).Select
ActiveCell.ClearContents

End If

I'm a bit swamped with work so don't have the time to modify it for you but here's Gary's Student's answer on finding the next: https://stackoverflow.com/a/30381177/19353309 . Don't forget to declarations of the variables. Also, try to avoid `Select`, for example: `ActiveCell.Offset(0,1).Select` and `ActiveCell.ClearContents` become `ÀctiveCell.Offset(0,1).ClearContents` see https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1 — Notus_Panda, Feb 01 '23 at 10:31

score 1 · Accepted Answer · answered Feb 01 '23 at 12:23

Please, try using the next updated code and send some feedback:

Sub FindMultipleTimes()
   Dim SearchValue6 As String 'located B9
   Dim Action6 As Range 'clear
   SearchValue6 = Workbooks.Open("C:\Users\.......xlsm").Worksheets("Sheet1").Range("B9").Value
    
   Dim ws As Worksheet: Set ws = Worksheets(2)
   Dim firstAddress As String
   Set Action6 = ws.Columns("A:A").Find(What:=SearchValue6, After:=ws.Range("A1"), LookIn:=xlFormulas2, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)

   If Not Action6 Is Nothing Then
            firstAddress = Action6.address
            Do
                    Action6.Offset(0, 1).ClearContents
                    Set Action6 = ws.Columns("A:A").FindNext(Action6) 'find the next occurrence
            Loop While Action6.address <> firstAddress
   Else
        MsgBox SearchValue6 & " could not be found in column ""A:A"" of sheet " & ws.name
   End If
End Sub

I only adapted your code, but do you want letting the workbook necessary to extract SearchValue6 value, open?

VBA Excel Find string in column and offset delete and repeat

1 Answers1