Index() avec projection des données et mef

Const COL_KP As Long = 6
Const COL_CLE As Long = 9
Private Sub Worksheet_Change(ByVal Target As Range)
Dim S As Worksheet
Dim Cible As Range
Dim Lig&
Dim Cle&
Dim A$
Dim R As Range
Dim var
Dim i&
On Error GoTo Erreur
'--- Contrôle de validité ---
If Target.Column = COL_KP Then
  If Target = "" Or Target.Offset(0, COL_CLE - COL_KP) = "" Then Exit Sub
  Set Cible = Target.Offset(0, 2)
ElseIf Target.Column = COL_CLE Then
  If Target = "" Or Target.Offset(0, COL_KP - COL_CLE) = "" Then Exit Sub
  If LCase(Target) <> "cléa" And LCase(Target) <> "cléb" Then Exit Sub
  Set Cible = Target.Offset(0, -1)
Else
  Exit Sub
End If
Lig& = Target.Row
'--- Distinction des Clés ---
A$ = LCase(Range("I" & Lig& & ""))
If A$ = "cléa" Then
  Cle& = 7
ElseIf A$ = "cléb" Then
  Cle& = 15
End If
'--- Vire les espaces ---
A$ = Range(Cells(Lig&, COL_KP), Cells(Lig&, COL_KP))
A$ = Replace(A$, Space(1), "")
'--- Recherche dans la feuille "Base" ---
Set S = Sheets("Base")
Set R = S.Range("f1:f" & S.Range("f65536").End(xlUp).Row & "")
var = R
For i& = 1 To UBound(var, 1)
  If A$ = var(i&, 1) Then
    S.Range(S.Cells(i&, Cle&), S.Cells(i&, Cle&)).Copy
    Application.EnableEvents = False
    Cible.PasteSpecial xlPasteAll
    Exit For
  End If
Next i&
Erreur:
Application.EnableEvents = True
Application.CutCopyMode = False
End Sub