Recherche du nom et extraire sa valeur

Option Explicit
Sub travdem()
Dim Cellule1 As Range, Plg1 As Range
Dim Nomfeuille1 As String, Col1 As String
Dim Nomfeuille2 As String, Col2 As String
Dim MonTab As Variant, Compt1 As Long
'parametre
Nomfeuille1 = "Feuil1"
Col1 = "a"
Nomfeuille2 = "Feuil2"
Col2 = "B"

'code
With Sheets("Feuil2")
Set Plg1 = .Range(Col2 & "2:C" & .Range(Col2 & .Rows.Count).End(xlUp).Row)
MonTab = Plg1.Value
End With

With Sheets("Feuil1")
For Each Cellule1 In .Range(Col1 & "2:" & Col1 & .Range(Col1 & .Rows.Count).End(xlUp).Row)
    For Compt1 = LBound(MonTab, 1) To UBound(MonTab, 1)
        If MonTab(Compt1, 1) = Cellule1 Then
            If MonTab(Compt1, 2) <> "" Then
                Cellule1.Offset(0, 1) = MonTab(Compt1, 2)
            End If
            Exit For
        End If
    Next Compt1
Next Cellule1

End With
End Sub