Multiple replace in Excel - macro ?

[andrei]

Hi Guys ,

I have a an excel book . In column A : words . In column B also words
synonyms for words in A column . Word(s) from B1 cell is/are synonym(s
for what is in A1 cell . And so on

In D column i have text , a lot of text , in every cell .

The macro should read every cell in D column and , if it finds word
that are also in A column , it should replace that word wit
corresponding word (synonym ) from B column . I give an example :

Let's say the result should be put in E column


A1: apartment B1 : dwelling D1: My mother enjoys his apartment
A2: enjoys B2 : likes

E1 should be : My mother likes his dwelling


Something like that

Many thanks

 

[Rick Rothstein]

Give this macro a try...

Sub SynonymReplacements()
Dim X As Long, LastRow As Long
Const StartRow As Long = 2
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
For X = StartRow To LastRow
Columns("D").Replace What:=Cells(X, "A").Value, _
Replacement:=Cells(X, "B").Value, _
LookAt:=xlPart, MatchCase:=False
Next
End Sub

 

[andrei]

it works . many thanks !