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
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?