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