Sub Recherche(r As Range, tout As Boolean)
Dim fichier$, c As Range, rr&, rc%, t, x As Variant
fichier = ThisWorkbook.Path & "\[Classeur1.xlsx]" 'à adapter
With r.Parent
Set r = Intersect(r, .Rows("6:" & .Rows.Count), .UsedRange)
If r Is Nothing Then Exit Sub
For Each c In r.Areas 'pour n'avoir qu'une zone
Set r = .Range(r, c)
Next
End With
rr = r.Row - 1: rc = r.Column - 1
t = r.Resize(, r.Columns.Count + 1).Formula 'matrice, plus rapide
Application.DisplayAlerts = False
On Error Resume Next
For Each c In r
If Not tout Or c.Column Mod 2 = 0 Then
If c.Column Mod 2 Then Set c = c(1, 2)
'matricule en C1 => RECAP!RC3
x = ExecuteExcel4Macro("VLOOKUP(RECAP!R1C3,'" & fichier _
& c(1, 0).Text & "'!R4C1:R10000C81,81,0)")
t(c.Row - rr, c.Column - rc) = IIf(IsError(x), "", x)
End If
Next
Application.EnableEvents = False
r.Resize(, r.Columns.Count + 1) = t
Application.EnableEvents = True
End Sub