Search and replace multiple values within string

Question

Looking for an automated solution (vba).

I have a dynamic list of old criteria (can be alphanumeric) and a list of new ones adjacent to it.

I need to automate a lookup/search and replace based on the list.

List has a named range if that helps make it faster.

Based on the lists in sheet "check", I need to search column L in sheet "audit" and replace any instance of the old numbers with the new numbers.

Answer 1

A tip for the future: When including screen shots, it helps if both Column Id's and Row Id's are included. You can temporarily hide Columns and/or Rows to achieve this. I assumed that the data commences on row 3 in your screen shot so see the comment in my code where you might need to edit the start row.

Ensure that you have a backup of your workbook in case the code does not perform as required.

Sub FindAndReplace() 

    Dim wsAudit As Worksheet 

    Dim wsCheck As Worksheet 

    Dim rngReplace As Range 

    Dim rngOld As Range 

    Dim rngCel As Range 

    Dim rngToFind As Range 

    Set wsAudit = Worksheets("Audit") 

    Set wsCheck = Worksheets("Check") 

    With wsAudit 

        Set rngReplace = .Range(.Cells(2, "L"), .Cells(.Rows.Count, "L").End(xlUp)) 

    End With 

    With wsCheck 

        'Edit 3 in following line to your correct start row for Old list 

        Set rngOld = .Range(.Cells(3, "AA"), .Cells(.Rows.Count, "AA").End(xlUp)) 

    End With 

    For Each rngCel In rngOld 

        With rngReplace 

            Set rngToFind = .Find(What:=rngCel.Value, _ 

                                LookIn:=xlValues, _ 

                                LookAt:=xlWhole, _ 

                                SearchOrder:=xlByRows, _ 

                                SearchDirection:=xlNext, _ 

                                MatchCase:=False, _ 

                                SearchFormat:=False) 

            If Not rngToFind Is Nothing Then 

                Do 

                    rngToFind.Value = rngCel.Offset(0, 1).Value 

                    Set rngToFind = .FindNext(rngToFind) 

                    If rngToFind Is Nothing Then Exit Do 

                Loop 

            End If 

        End With 

    Next 

End Sub 

Feel free to get back to me if code does not perform as required.

Answer 2

Works Great!

When I make my first million.... I'm buying you a Krystal burger WITH cheese my friend!

Or a White Castle burger if you prefer.

With the number of these you have promised me, you might need to make more than a million. Anyway, I am happy to be able to assist you. Have a good day.

Answer 3

Works Great!

When I make my first million.... I'm buying you a Krystal burger WITH cheese my friend!

Or a White Castle burger if you prefer.