When you are data cleansing within your VBA routine a common action you might need to do is re-map text values to a different name so they match another dataset you’re joining them to.
You can do this in VBA without having to specify each individual value in the code. In this example we have a list of existing values along with the new value we want to replace them with.
Select your data and hit Ctrl + T to turn it into an Excel table. Name the table TableFindReplace.
We can then apply this code which will check one-by-one for all instances of the values in the Existing column to replace them with the value in the New column. E.g. all instances of Man C to Manchester City, all instances of Man U to Manchester United, etc.
NB: You will need to adjust “SheetName” with your worksheet name and “TableData” with the table name containing your data.
Dim FindReplaceList As Range
Dim Results As Range
Set FindReplaceList = Sheets(“SheetName”).ListObjects(“TableFindReplace”).DataBodyRange
Set Results = Sheets(“SheetName”).ListObjects(“TableData”).DataBodyRange
For Each cell In FindReplaceList.Columns(1).Cells
Selection.Replace what:=cell.Value, Replacement:=cell.Offset(0, 1).Value