Find and replace multiple values within selected cells

I want to run a find and replace on multiple values within a range in excel with values from 2 columns: A with the original word; B with the translation. I already found the VBA code to get this working for 50%, but this code runs it on the whole worskheet.

Ideally I would like to be able to run it on just the range I selected. An added bonus would be if I could also select the lookup range.

This is what I'm using this far. Thanks for your help!

Sub abbrev() Dim abvtab() As Variant Dim ltsheet As Worksheet Dim datasheet As Worksheet Dim lt As Range 'Change Lookup to the sheet name with your lookup table. Set ltsheet = Sheets("Lookup") 'Change Data to the sheet name with your data. Set datasheet = Sheets("Data") 'Change A2 to the top left cell (not the header) in your lookup table. 'Change B2 to top right cell. Set lt = ltsheet.Range("A1", ltsheet.Range("B1").End(xlDown)) abvtab = lt For i = 1 To UBound(abvtab) datasheet.Cells.Replace What:=abvtab(i, 1), Replacement:=abvtab(i, 2), LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _ ReplaceFormat:=False Next i
End Sub

1 Answer

Excel Multi Replace

1. Open your VBA editor (Alt+F11) and paste the below macro anywhere
2. Set up a two-column lookup range: 1st column is the value to search for, 2nd the value to replace
3. Select your input range where values should be replaced like shown in the 1st picture
4. Execute the macro (Alt+F8).

The macro asks where your Lookup range is. First the sheet name, then the Lookup range address. Enter only the first column e.g A1:A2 for the example below.

That's it. Now the macro begins to iterate through all replace rules and applies them like a
normal Excel Search & Replace (Ctrl+H) onto your selected input range.

Input range Replace rules Input range after macro

Sub MultiReplace()
On Error GoTo errorcatch
Dim arrRules() As Variant strSheet = InputBox("Enter sheet name where your replace rules are", _ "Sheet name", "Sheet1") strRules = InputBox("Enter address of replaces rules." & vbNewLine & _ "But only the first column!", "Address", "A1:A100") Set rngCol1 = Sheets(strSheet).Range(strRules) Set rngCol2 = rngCol1.Offset(0, 1) arrRules = Application.Union(rngCol1, rngCol2) For i = 1 To UBound(arrRules) Selection.Replace What:=arrRules(i, 1), Replacement:=arrRules(i, 2), _ LookAt:=xlWhole, MatchCase:=True Next i
errorcatch:
End Sub