Clear contents before replacing text

[Denblanc]

Hello,
I have "Sheet1", would like to compare column "G" with Array then place results in column "D" is the end result trying to get to. Below is the code to compare column "G" with array and put replaced text in column "G". This code works but does not clear the old value out of the column first.

Could someone help me with a ClearContents command to fix column "G" or the correct code for my end result? Thanks!

Sub Multi_FindReplace()


Dim sht As Worksheet
Dim fnd As Variant
Dim rplc As Variant
Dim x As Long


fnd = Array("ABC", "DEFGHIJ", "KLM")
rplc = Array("NEW", "TODAY", "TOMORROW")


'Loop through each item in Array lists
  For x = LBound(fnd) To UBound(fnd)
     For Each sht In ActiveWorkbook.Worksheets
         sht.Cells.Replace What:=fnd(x), Replacement:=rplc(x), _
          LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False
          
     Next sht
  Next x
  
End Sub

[mikerickson]

This code works but does not clear the old value out of the column first.

Why mess with success?

I know I was emphatic, but "this code works" is the whole ball game.

[Denblanc]

Except it puts the new text over the old text, so if the old text is "America" and new text is "News" end result is "Newsica"

[mikerickson]

I don't see "Amer" on your list of Find terms.

Perhaps I'm not understanding the problem

If you use Replace, it doesn't restrict itself to whole words. Find "cat" replace "dog" will turn "catalog" into "dogalog"

The usual work-around is to put a leading and a trailing space into each cell and then search for " cat " and replace it with " dog ". And then remove the extra spaces.

Is that the issue?


Edit: if there is only one word per cell, you could change the argument to LookAt:=xlWhole and not have to fiddle with spaces.

[SamT]

Use LookAt:=xlWhole

[Denblanc]

Here is the problem:

column G replacement text end result

tomorrow today todayrow problem-wrong result
tomorrow today today end result wanted

[Denblanc]

To: SamT
I have looked and flipped at so many codes I think my brain is fried. I knew this was going to be a snake in front of me, it replaces everything but the very first cell.

[Denblanc]

Thank you for your assistance, I figured out the rest. Knew I was close to solution but too simple to see.