1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
| Sub DictionnaireV2()
Dim T, truc, temp, i
Dim mondico As Object
Dim mondicoCommun As Object
T = Timer
Set mondico = CreateObject("Scripting.Dictionary")
Set mondicoCommun = CreateObject("Scripting.Dictionary")
Set s1 = Worksheets("Feuil1")
Set S2 = Worksheets("Feuil2")
If S2.UsedRange.Rows.Count < s1.UsedRange.Rows.Count Then
Set SR = s1
Set s1 = S2
Set S2 = SR
Set SR = Nothing
End If
Dim TabloS1 As Variant
Dim TabloS2 As Variant
TabloS1 = Range(s1.Range("A1"), s1.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1))
TabloS2 = Range(S2.Range("A1"), S2.Cells(Rows.Count, 1).End(xlUp).Offset(0, 1))
For i = 1 To UBound(TabloS1, 1)
If Not mondico.Exists(TabloS1(i, 1)) Then mondico.Add TabloS1(i, 1), TabloS1(i, 1)
Next i
For i = 1 To UBound(TabloS2, 1)
If mondico.Exists(TabloS2(i, 1)) Then
If Not mondicoCommun.Exists(TabloS2(i, 1)) Then mondicoCommun.Add TabloS2(i, 1), TabloS2(i, 1)
TabloS2(i, 2) = "OK"
Else
TabloS2(i, 2) = "ko"
End If
Next i
For i = 1 To UBound(TabloS1, 1)
If mondicoCommun.Exists(TabloS1(i, 1)) Then
TabloS1(i, 2) = "OK"
Else
TabloS1(i, 2) = "ko"
End If
Next i
Debug.Print Timer - T
Debug.Print mondico.Count
Debug.Print mondicoCommun.Count
Application.StatusBar = mondicoCommun.Count
Application.ScreenUpdating = False
s1.Range("A1:b" & UBound(TabloS1)) = TabloS1
S2.Range("A1:b" & UBound(TabloS2)) = TabloS2
Application.ScreenUpdating = True
Debug.Print "DictionnaireV2 :"; Timer - T
MsgBox "DictionnaireV2 :" & vbCr & Timer - T
End Sub |
Partager