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
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