Sub MyFormule()
For k = 2 To [A65536].End(3).Row
If Cells(k, 1) <> "" And IsDate(Cells(k, 3)) And Cells(k, 5) = "" Then
dx = Cells(k, 3)
nomfichier = Split(dx, "/")(2) & "-" & Split(dx, "/")(1) & "-" & Split(dx, "/")(0)
chemin = "D:\essai excel\[" & nomfichier & ".xlsx]Sheet1'!$A$2:$E$65000,5,FALSE)"
Cells(k, 5).Formula = "=VLOOKUP(" & Cells(k, 1) & ",'" & chemin
Cells(k, 5).Value = IIf(Cells(k, 5) = "#N/A", "", Cells(k, 5).Value) 'mets que la valeur et supp #N/A
End If
Next
End Sub