The below VBA code, posted here by the Spreadsheet Guru, can be used to find and replace multiple values in an Excel workbook. List the terms you want to find in column A of 'Sheet1' and the terms they should replace in column B. Select the ranges (not the whole columns) and then press CTRL + T to add the data as 'Table1', the default name.
Put the VBA code in a new module.
Go to View . . . Macros and run the code.
The macro will replace anywhere the listed terms appear on other worksheets. So this worksheet:
. . . is changed to:
This macro will work for strings which don't consist of the entire contents of a cell.
'PURPOSE: Find & Replace a list of text/values throughout entire workbook from a table
Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant
'Create variable to point to your table
Set tbl = Worksheets("Sheet1").ListObjects("Table1")
'Create an Array out of the Table's Data
Set TempArray = tbl.DataBodyRange
myArray = Application.Transpose(TempArray)
'Designate Columns for Find/Replace data
fndList = 1
rplcList = 2
'Loop through each item in Array lists
For x = LBound(myArray, 1) To UBound(myArray, 2)
'Loop through each worksheet in ActiveWorkbook (skip sheet with table in it)
For Each sht In ActiveWorkbook.Worksheets
If sht.Name <> tbl.Parent.Name Then
sht.Cells.Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _