VBA Excel Find string in column and offset delete and repeat-CodePudding

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

CodePudding user response：

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?

CodePudding user response：

I would recommend not using the Find function for this, but just use If inside a loop.

Not sure in which direction you wanted the offset, but here is an example. I'm sure you can figure out how to adjust it to your needs.

Function FindEmptyAndOffset()

Dim SearchValue6 As String 'located C9
Dim cell, searchRange As Range
SearchValue6 = Range("C9").Value

Set searchRange = Range("A1:A14")

For Each cell In searchRange

    If (cell.Value = SearchValue6) Then
        cell.Offset(0, 1).Value = cell.Value
        cell.ClearContents
        
    End If

Next

End Function

Notice I have moved the search cell to C9, so that we can offset values to column B. Also take notice that I have reduced the search area from the entire column to a specific range, you might want to adjust this also.