multiple replacement macro

[k.03a]

Hi all,

I am looking for a way to replace a large number of text values in the whole workbook with other text values?

In a very large excel file, I have a list of 1000 European towns which I want to replace by a list of 1000 US towns. I have a table with the corresponding towns as below:
Europe US
London New York
Paris Los Angeles
etc...

Is there a macro that can be written to replace all values in the workbook corresponding to A2 by B2, A3 by C3, A4 by C4 etc...?

Many thanks for your help !

 

[Misca]

Here's one way to do it:

First have the lists like this on one sheet:

Excel Workbook
	A	B
1	EUROPE	US
2	London	New York
3	Paris	Los Angeles
Sheet

Then paste this code to your VBA module:

Sub ReplaceCityNames()

Dim c As Range
Dim Europe As Range
Dim WS As Worksheet
Dim ListSheet As Worksheet

Set ListSheet = Sheets("Sheet3")    'The sheet where the Europe - US lists are found

With ListSheet
    Set Europe = .Range(.Range("A2"), .Range("A" & .Rows.Count).End(xlUp))  'Just the A column
End With

For Each c In Europe    'Loops through the cities in the list
    For Each WS In Worksheets
    'Doesn't replace the cities on the ListSheet (= the original city list remains unchanged)
        If WS.Name <> ListSheet.Name Then
            WS.Cells.Replace What:=c.Value, Replacement:=c.Offset(0, 1).Value, LookAt:=xlPart, _
                    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                    ReplaceFormat:=False
        End If
    Next WS
Next c

End Sub

 

[k.03a]

Awesome! Thanks