Find Text in One Row and Replace With Text Based on Value in Another Row

[FrenchCelt]

Hello,

I searched around for solutions first, but none quite fit the situation I have here. I'm trying to find and replace all text in Row 3 that says "Nose" with either "4STOP" or "3STOP", but it depends on the value in Row 10 of the same column. If it says 4, then replace with 4STOP, if 3 then 3STOP.

Any help would be appreciated.

 

[fadee2]

try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("a3") = "Nose" Then
    ActiveSheet.Range("a3") = ActiveSheet.Range("a10") & "stop"
End If
End Sub

under worksheet object in vba...

HTH

 

[Fluff]

How about

Sub FrenchCelt()
   With Range("A3", Cells(3, Columns.Count).End(xlToLeft))
      .Value = Evaluate(Replace("if(@="""","""",if(@=""nose""," & .Offset(7).Address & "&""STOP"",@))", "@", .Address))
   End With
End Sub

 

[eirikdaude]

try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Range("a3") = "Nose" Then
    ActiveSheet.Range("a3") = ActiveSheet.Range("a10") & "stop"
End If
End Sub

under worksheet object in vba...

HTH

This does nothing even resembling what's asked in the OP, and asking for it to be placed in Worksheet_SelectionChange borders on harmful advice.

 

[FrenchCelt]

Thanks Fluff. I did have to change your A3 to B3, as Column A contains labeling info for each of the rows (the data is basically in table form). Once I did that, everything worked out perfectly.

 

[Fluff]

Glad we could help & thanks for the feedback.