Sayfanın kod bölümüne alttaki kodları ekleyin alternatif olarak.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim dict As Object, son As Long, veri(), i As Long

    

    Set dict = CreateObject("Scripting.dictionary")

    dict.comparemode = 1 '1 kücük büyük harf icin

    

    With ThisWorkbook.Sheets("Sayfa2")

        son = .Range("A" & Rows.Count).End(3).Row

        veri = .Range("A1:B" & son).Value

        If Target.Column = 1 Then

            If Target.Cells.Count = 1 Then

                For i = LBound(veri) To UBound(veri)

                    If Not dict.exists(veri(i, 1)) Then dict.Add veri(i, 1), veri(i, 2)

                Next

                Target.Offset(0, 1).Value = dict(Target.Value)

            End If

        End If

    End With

    Set dict = Nothing: Erase veri

End Sub