Find and Replace based on a lookup table

[apike36]

Hi

This is my first time on the site so hopefully someone can help!

I have two sheets in a workbook. The first contains various text strings in column A which have a reference number within them

E.g.
A1 = 'hhdkjhdk123456'
A2 = 'hfjud654321kkkkddhjb'
A3 = 'uyeh234987'
and so on..

The second sheet contains two columns; the first column with values I want to find in Sheet 1 and the second column being the value I want to replace in Sheet 1

E.g.
A1= 123456 B1= 555555
A2= 654321 B2= 666666
C3= 234987 C3= 777777
and so on..

I therefore want a macro which when run will result in Sheet 1 appearing as follows:

A1 = 'hhdkjhdk555555'
A2 = 'hfjud666666kkkkddhjb'
A3 = 'uyeh777777'

I have found advise on the site on how to do this with one lookup (i.e. find text that equals what is in A1 and replace with A2) but cannot find how to loop this to then find A2, A3 etc...

Thanks

 

[AlphaFrog]

[color=darkblue]Sub[/color] [color=darkblue]Sub[/color]stitutions()
    
    [color=darkblue]Dim[/color] rngData     [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] rngLookup   [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] Lookup      [color=darkblue]As[/color] Range
    
    [color=darkblue]With[/color] Sheets("Sheet1")
        [color=darkblue]Set[/color] rngData = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    End [color=darkblue]With[/color]
    
    [color=darkblue]With[/color] Sheets("Sheet2")
        [color=darkblue]Set[/color] rngLookup = .Range("A1", .Range("A" & Rows.Count).[color=darkblue]End[/color](xlUp))
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] Lookup [color=darkblue]In[/color] rngLookup
        [color=darkblue]If[/color] Lookup.Value <> "" [color=darkblue]Then[/color]
            rngData.Replace What:=Lookup.Value, _
                            Replacement:=Lookup.Offset(0, 1).Value, _
                            LookAt:=xlPart, _
                            SearchOrder:=xlByRows, _
                            MatchCase:=[color=darkblue]False[/color]
        End [color=darkblue]If[/color]
    [color=darkblue]Next[/color] Lookup
    
End Sub

 

[apike36]

Thanks AlphaFrog!!!