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
searchRange
, but no loop for yourfoundCell
. In other words you are comparing a cell to an array of cells. Instead of a loop, can't you just do some kind of filter, then select all visible cells within whatever range you specify, then clearcontent?