Find and Replace in different cell

[cmschmitz24]

Can someone help me with a tweak to the find and replace code to replace in a different cell.

Range(Selection, Selection.End(xlDown)).Select
    Cells.Replace What:="00600629", Replacement:="Christina", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Find 00600629 in column E
Replace with Christina in column F, same cell number

 

[cmschmitz24]

Or an IF THEN statement might be able to be used instead -

 

[ranitha3]

Hi,
Try the below code,

Sub FindReplace()
Range("E1").Activate
Do Until ActiveCell.Value = ""
If InStr(1, ActiveCell.Value, "00600629") > 0 Then
ActiveCell.Offset(0, 1).Value = "Christina"
End If
ActiveCell.Offset(1, 0).Activate
Loop
End Sub

 

[Fluff]

Another option

Sub OffsetReplace()

   With Range("F2", Range("F" & Rows.Count).End(xlUp))
      .Value = Evaluate("IF(ISNUMBER(FIND(""00600629""," & .Offset(, -1).Address & ")),""Christina""," & .Address & ")")
   End With
End Sub

 

[cmschmitz24]

Another option

Sub OffsetReplace()

   With Range("F2", Range("F" & Rows.Count).End(xlUp))
      .Value = Evaluate("IF(ISNUMBER(FIND(""00600629""," & .Offset(, -1).Address & ")),""Christina""," & .Address & ")")
   End With
End Sub

Fluff, this worked perfectly! Thanks so much!

 

[Fluff]

Glad to help & thanks for the feedback.