Replace all words by new word from another cell

[frankdethier]

hi

I need to replace all words in an excel tab ''change initiative'' by a customer specific word, e.g. how the initiative is called at the customers. For example ''BOOST''. But ''Boost'' is not fixed and is a value coming from a survey, so I cannot hard code it in VBA. Is there a way to have VBA code referring to value in particular cell, like B5? Now BOOST is copied into cell B5.

I started with this code, but that obviously doesn't work cause it's replacing everywhere change initiative by ''B2'' --> how can I replace it with the value of cell B2?

Sub TestReplace()
Sheets("Sheet1").UsedRange.Replace What:="change initiative", Replacement:="B2"
End Sub

 

[mohadin]

Should be

Sub TestReplace()
Sheets("Sheet1").UsedRange.Replace What:="change initiative", Replacement:=Range("B2")
End Sub

 

[fadee2]

Another Aproach

Sub TestReplace1()
Dim rng As Range
Set rng = Range("A1:f1")
For Each cell In rng
    If cell.Value = "change initiative" Then
        cell.Replace What:="change initiative", Replacement:=Range("b2")
    End If
Next cell
End Sub

 

[frankdethier]

thank you!

that works ... have a nice day