find replace

[sudburymetoo]

I want to search find multiple names like, John bering, Soft ware, jack nicholson,route 1, in range a1 to z1000 and replace , for each instance of that name replace with terminated. Any help with a macro ? Thanks

 

[brettdj]

To match whole strings with case you could use this

Flesh out the array of strings to be replaced as needed

For a partial string match use
XlPart
rather than
XlWhole

for case insensitive use
False
rather than
True

Cheers
Dave

Sub Terminate()
    Dim StrArray
    Dim StrElem
    Dim rng1 As Range
    Set rng1 = Range([a1], [z1000])
    StrArray = Array("John bering", "Soft ware", "jack nicholson", "route 1")
    For Each StrElem In StrArray
        rng1.Replace StrElem, "terminated", xlWhole, , True
    Next
End Sub

 

[sudburymetoo]

this is cool,works perfect.Thanks