VBA Search/replace with offset 1 column and offset 2 column

[Scorpion Steve]

Hi all.
I am trying to work out how to nest offset in a code using search replace.
The goal is to find a value from cell A1 (example) and replace the values in the cells next to the cell containing samuel and the cell containing samuel.
A sort of search and remove data tool if you like
So for example:

A1: "samuel" (the search criteria)
Search range is: B1:D400
(for example)
Cell B40 = samuel
C40 = Driver
D40 = year
So, the macro is activated and finds "samuel" in B40 and I would like C40 & D40 replaced with ""
The code I'm using is below: (this is just replacing the cell containing the search criteria with "test".
I would like to nest offset(0,2) & offset(0,3).Value = ""

Sub Macro1()
Range("B1:D400").Select
Selection.Replace What:=Sheets("Sheet1").Range("A1").Value, Replacement:="test", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False




End Sub

Thanks

 

[circledchicken]

Hi,

Perhaps try a pattern like this:

Sub example()


Dim strA    As String
Dim oRng    As Range
Dim rNa     As Range
Dim i       As Long
Dim j       As Long


strA = Range("A1") ' string to search
Set oRng = Range("B1:D400") ' range to search in


i = WorksheetFunction.CountIf(oRng, strA) ' count of string in range
Set rNa = oRng(1, 1)
For j = 1 To i
    Set rNa = oRng.Find( _
                What:=strA, _
                After:=rNa, _
                LookIn:=xlValues, _
                LookAt:=xlWhole, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, _
                MatchCase:=False)
    
    rNa.Offset(0, 1).Resize(1, 2) = ""
Next j


End Sub

This pattern and other useful uses for loops can be found here:
Excel VBA Loops: Correct/Efficient Uses of Excel Loops. Do, For Each and While Loops

 

[JoeMo]

If I understand correctly what you want to do, the Range.Replace method is not going to do it because it doesn't identify the specific cells containing the search string. Try using the Range.Find method instead.