Option Explicit

Sub ChangeOnPattern()

Const PatternColNum =  9          'номер колонки со значениями, что искать
Const ReplaceWithColNum =  10     'номер колонки со значениями, на что менять
Const WhereToReplaceColNum =  1   'номер колонки , где искать и менять

Dim WithPatternSheetName As String
Dim WhereToReplaceSheetName As String

Dim Pattern As String
Dim RowIndex As Long

Dim SearchResult As Range
Dim PSh As Worksheet
Dim SSh As Worksheet
    
Dim firstAddress As String

    WhereToReplaceSheetName = "Лист1"   'имя листа, где искать и менять
    WithPatternSheetName = "Лист1"      'имя листа со значениями, что искать и на что менять
    
    Set SSh = Worksheets(WhereToReplaceSheetName)
    Set PSh = Worksheets(WithPatternSheetName)
    
    RowIndex =  3                         'с этой строки идут значения шаблонов
    
    Pattern = PSh.Cells(RowIndex, PatternColNum).Value
    
    Do Until Pattern = ""

        Set SearchResult = SSh.Columns(WhereToReplaceColNum).Find(Pattern)
        
        Set SearchResult = SSh.Columns(WhereToReplaceColNum).Find(Pattern)
        If Not SearchResult Is Nothing Then
            firstAddress = SearchResult.Address
            Do
                SearchResult.Value = PSh.Cells(RowIndex, ReplaceWithColNum).Value
                Set SearchResult = SSh.Columns(WhereToReplaceColNum).FindNext(SearchResult)
                If SearchResult Is Nothing Then Exit Do
            Loop While Not SearchResult Is Nothing And SearchResult.Address <> firstAddress
        End If
        
        RowIndex = RowIndex +  1 
        Pattern = PSh.Cells(RowIndex, PatternColNum).Value
    Loop

    Set SSh = Nothing
    Set PSh = Nothing
    Set SearchResult = Nothing
End Sub

        Set SearchResult = SSh.Columns(WhereToReplaceColNum).Find(Pattern)
        
        Set SearchResult = SSh.Columns(WhereToReplaceColNum).Find(Pattern)

        Set SearchResult = SSh.Columns(WhereToReplaceColNum).Find(Pattern)