Option Explicit
Sub ventile()
Dim a, i As Long, j As Long, k As Byte, txt As String
Dim ws As Worksheet, dico As Object
'creation du dictionnaire parent
Set dico = CreateObject("Scripting.Dictionary")
dico.CompareMode = 1
With Sheets("Data Données Brute")
For i = 2 To 801 Step 20
'plage à traiter
a = .Range("A" & i & ": L" & i + 19).Value
'clé = nom de la feuille de destination
txt = "V 1000 % " & Left(a(2, 12), 2)
If Not dico.exists(txt) Then
'creation du dictionnaire enfant
Set dico(txt) = CreateObject("Scripting.Dictionary")
dico(txt).CompareMode = 1
End If
'clé = n° de la réunion & n° de la course
If Not dico(txt).exists(a(2, 12)) Then
'creation du dictionnaire petit enfant
Set dico(txt)(a(2, 12)) = CreateObject("Scripting.Dictionary")
dico(txt)(a(2, 12)).CompareMode = 1
End If
For k = 1 To UBound(a, 1)
If Not IsEmpty(a(k, 6)) Then
'clé = n° d'ordre d'arrivée converti pour la circonstance
a(k, 6) = a(k, 6) & IIf(a(k, 6) = 1, "er", "è")
If Not dico(txt)(a(2, 12)).exists(a(k, 6)) Then
'on affecte l'element soit le n° du cheval situé en colonne 11
dico(txt)(a(2, 12))(a(k, 6)) = a(k, 11)
End If
End If
Next
Next
End With
Application.ScreenUpdating = False
For Each ws In Worksheets
If dico.exists(ws.Name) Then
With ws.Range("b27").CurrentRegion
With .Offset(2, 1).Resize(.Rows.Count - 2, .Columns.Count - 1)
.ClearContents
End With
For i = 3 To .Rows.Count
If dico(ws.Name).exists(.Cells(i, 1).Value) Then
For j = 2 To .Columns.Count
If dico(ws.Name)(.Cells(i, 1).Value).exists(.Cells(2, j).Value) Then
.Cells(i, j).Value = dico(ws.Name)(.Cells(i, 1).Value)(.Cells(2, j).Value)
End If
Next
End If
Next
End With
End If
Next
Set dico = Nothing
Application.ScreenUpdating = True
End Sub