PDA

View Full Version : [SOLVED] Clear contents before replacing text



Denblanc
02-16-2016, 06:07 PM
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
02-16-2016, 06:33 PM
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
02-16-2016, 06:46 PM
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
02-16-2016, 06:53 PM
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
02-16-2016, 07:01 PM
Use LookAt:=xlWhole

Denblanc
02-16-2016, 07:02 PM
Here is the problem:

column G replacement text end result

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

Denblanc
02-16-2016, 07:10 PM
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
02-16-2016, 07:57 PM
Thank you for your assistance, I figured out the rest. Knew I was close to solution but too simple to see.