Search and replace in excel based on a list

0
votes

I need to replace values on Sheet1 based on a table on Sheet2.

  • Sheet1 has a long list of names all over the sheet in different columns that I need to replace with standardized names based on the table on Sheet2.
  • On Sheet2, column A has the values to be replaced and column B has the values to be replaced with.

I use Ms Excel 2011 for Mac. Is there a simple VBA macro for it?

1
vbaexcelreplace

1 Answers

0
votes
Option Explicit

Public Sub searchAndReplaceFromList()

    Dim cel As Range

    With Sheet2

        For Each cel In Sheet2.Range("A1:A" & .Cells(.Rows.Count, 1).End(xlUp).Row)

            Sheet1.UsedRange.Replace cel.Value2, cel.Offset(0, 1).Value2

        Next

    End With

End Sub