Sub RechercheTableau()
A = [A1:b20000]
t = Timer()
For j = 15000 To 16000 Step 2 ' 500 recherches= 4 sec
x = "Nom" & Trim(Str(j))
For i = 1 To 20000
If A(i, 1) = x Then
y = A(i, 2)
End If
Next i
Next j
MsgBox Timer() - t
End Sub
Sub RechercheDico()
Set mondico = CreateObject("scripting.dictionary")
A = [A1:b20000]
For i = 1 To 20000
mondico(A(i, 1)) = A(i, 2)
Next i
t = Timer()
For j = 15000 To 16000 Step 2 ' 500 recherches= 0,015 sec
x = "Nom" & Trim(Str(j))
y = mondico(x)
Next j
MsgBox Timer() - t
End Sub
Sub RechercheFind()
t = Timer()
For j = 15000 To 16000 Step 2 ' 500 recherches= 2,65 sec
x = "Nom" & Trim(Str(j))
Set result = [A1:A20000].Find(what:=x)
y = result.Offset(, 1)
Next j
MsgBox Timer() - t
End Sub
Sub MatchArray()
Dim VAR_TAB
VAR_TAB = Range("A1:A20000")
t = Timer()
For j = 15000 To 16000 Step 2 ' 500 recherches= 21 sec
Val_Cherchee = "Nom" & Trim(Str(j))
y = Application.Match(Val_Cherchee, Application.Index(VAR_TAB, 0, 1), 0)
Next j
MsgBox Timer() - t
End Sub
Sub MatchTableur()
Dim VAR_TAB
t = Timer()
For j = 15000 To 16000 Step 2 ' 500 recherches= 2 sec
Val_Cherchee = "Nom" & Trim(Str(j))
y = Application.Match(Val_Cherchee, Application.Index(Range("A1:A20000"), 0, 1), 0)
Next j
MsgBox Timer() - t
End Sub