6

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
4

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

enter image description hereenter image description here enter image description here

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
| improve this answer | |
  • Run-time error '9': Subscript out of range :( – WouterB Jul 16 '13 at 16:44
  • In fact, it will only replace A1 for me. Also, if you replace the lookup range to: A1:A3, it will return the runtime error. – WouterB Jul 16 '13 at 16:50
  • The find and replace should replace all found values in each cell in the range I provide. Right now, it only works on call A1 for me... . Yes, that's fine. Thanks! – WouterB Jul 16 '13 at 16:55
  • See my edit. In my test scenario I selected A1:A4 on the first sheet and executed the macro. (lookup sheet name was Lookup and Lookup range was A1:A3). For me its working perfectly. It replaces everything. – nixda Jul 16 '13 at 17:11
  • Great scripts!. For anyone need to replace parts of text, just change LookAt:=xlWhole to LookAt:=xlPart – Wildan Muhlis Dec 7 '19 at 2:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.