Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
'date debut
If Target.Column = 4 Then
If Target.Offset(, -3) = "" Or Not IsDate(Target) Then Exit Sub
dx = Target.Value
dx = Replace(dx, "-", "/")
nomfichier = Split(dx, "/")(2) & "-" & Split(dx, "/")(1) & "-" & Split(dx, "/")(0)
chemin = "D:\essai excel\[" & nomfichier & ".xlsx]Sheet1'!$A$2:$E$65000,5,FALSE)"
With Target.Offset(, 2)
Application.EnableEvents = False
.Formula = "=VLOOKUP(" & Target.Offset(, -3).Address & ",'" & chemin
If Application.IsNA(.Value) = True Then
.Value = ""
Else
.Value = .Value
End If
Application.EnableEvents = True
End With
End If
'date fin
If Target.Column = 5 Then
If Target.Offset(, -4) = "" Or Not IsDate(Target) Then Exit Sub
dx = Target.Value
dx = Replace(dx, "-", "/")
nomfichier = Split(dx, "/")(2) & "-" & Split(dx, "/")(1) & "-" & Split(dx, "/")(0)
chemin = "D:\essai excel\[" & nomfichier & ".xlsx]Sheet1'!$A$2:$E$65000,5,FALSE)" 'revoir la fin de la ligne
With Target.Offset(, 2) 'c'est bon
Application.EnableEvents = False
.Formula = "=VLOOKUP(" & Target.Offset(, -4).Address & ",'" & chemin
If Application.IsNA(.Value) = True Then
.Value = ""
Else
.Value = .Value
End If
Application.EnableEvents = True
End With
End if
End Sub