While covering for my colleague at a PremierCare site, the marketing manager wanted to see if i could help him with some basic search and replace macros to eliminate the need for his manual data cleanup when he export reports from the database.

He wanted to change items codes into names (e.g 123 -> Hotel) and country common names to ISO 3166 2-letter (alpha-2) codes (e.g Japan -> JP).

For changing of common country names to alpha-2 codes:

Sub multiFindNReplaceCountries()
 Dim myList, myRange
 Set myList = Sheets("Country Codes").Range("A2:B273") 'two column range where find/replace pairs are
 Set myRange = Sheets("Report").Columns(1) 'range to be searched
 For Each cel In myList.Columns(1).Cells
 myRange.Replace what:=cel.Value, replacement:=cel.Offset(0, 1).Value
 Next cel
 End Sub

This select a list of country names from the worksheet labelled” Country Codes”, from Range A2 to B273. First column (A) is where i have the full country name, and 2nd column (B) is where i have the 2-alpha country code. There are a total of 273 entries, hence i select the range to B273.

The worksheet that i want to do the search and replace is called “Report”. The country names are all on the first column of the worksheet, hence in myRange, i have it set to Columns(1), indicating that the first column are the cells that the script needs to perform the search and replace.

For changing of ID codes to their respective descriptions:

Sub multiFindNReplaceCode()
 Dim myList, myRange
 Set myList = Sheets("Codes").Range("A2:B53") 'two column range where find/replace pairs are
 Set myRange = Sheets("Report").Rows(1) 'range to be searched
 For Each cel In myList.Columns(1).Cells
 myRange.Replace what:=cel.Value, replacement:=cel.Offset(0, 1).Value
 Next cel
 End Sub

This select a list of codes from the worksheet labelled “Codes”, from Range A2 to B53. First column (A) is where i have the codes, and 2nd column (B) is where i have the full descriptive names. There are a total of 53 entries, hence i select the range to B53.

The worksheet that i want to do the search and replace is called “Report”. The codes span horizontally across Row 1, hence in myRange, i have it set to Rows(1), indicating that the first row are the cells that the script needs to perform the search and replace.

Just insert a module using the VBA Macro editor in your excel (or press Alt + 11), past the above and change the worksheet names, run the script and you should see the values automatically updated.