re marielegrelle
A tester :
Option Explicit
Sub ventile()
Dim a, w(), i As Long, j As Long, dico As Object
Set dico = CreateObject("Scripting.Dictionary")
dico.CompareMode = 1
a = Sheets("Recap").Range("a1").CurrentRegion.Value
For i = 2 To UBound(a, 1)
If Not dico.exists(a(i, 2)) Then
Set dico(a(i, 2)) = CreateObject("Scripting.Dictionary")
dico(a(i, 2)).CompareMode = 1
End If
If Not dico(a(i, 2)).exists(a(i, 3)) Then
ReDim w(1 To 2, 1 To 1)
Else
w = dico(a(i, 2))(a(i, 3))
ReDim Preserve w(1 To UBound(w, 1), 1 To UBound(w, 2) + 1)
End If
w(1, UBound(w, 2)) = a(i, 1)
w(2, UBound(w, 2)) = a(i, 4)
dico(a(i, 2))(a(i, 3)) = w
Next
Application.ScreenUpdating = False
With Sheets("Analysis").Range("a1").CurrentRegion
With .Offset(2, 5).Resize(.Rows.Count - 2, .Columns.Count - 5)
.ClearContents
End With
For i = 3 To .Rows.Count Step 10
If dico.exists(.Cells(i, 2).Value) Then
For j = 6 To .Columns.Count Step 2
If dico(.Cells(i, 2).Value).exists(.Cells(1, j).Value) Then
'w = dico.Item(.Cells(i, 2).Value).Item(.Cells(1, j).Value)
.Cells(i, j).Resize(UBound(dico.Item(.Cells(i, 2).Value).Item(.Cells(1, j).Value), 2), UBound(dico.Item(.Cells(i, 2).Value).Item(.Cells(1, j).Value), 1)).Value = _
Application.Transpose(dico(.Cells(i, 2).Value)(.Cells(1, j).Value))
End If
Next
End If
Next
End With
Application.ScreenUpdating = True
End Sub
klin89