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 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79
| Sub Recap()
Dim C As Range, F, Dico As Object, Tabl1() As String, Tabl2() As Integer
Dim Res As String, Txt, Ligne As Long, Ctr As Long
Set Dico = CreateObject("Scripting.Dictionary")
Ligne = 1
With Sheets("Feuil1")
ReDim Tabl1(Application.CountA(.[E:E]) - 2)
ReDim Tabl2(Application.CountA(.[E:E]) - 2, 2)
Ctr = -1
For i = 2 To .Cells(.Rows.Count, 5).End(xlUp).Row
If .Cells(i, 5) <> "" Then
Ctr = Ctr + 1
Tabl1(Ctr) = .Cells(i, 5)
End If
Next i
F = Array("Feuil3", "Feuil4", "Feuil5")
.[E2:E65000].Clear
.[R2:T65000].Clear
End With
For i = 0 To 2
With Sheets(F(i))
For Each C In .Range(.[B2], .Cells(.Rows.Count, 2).End(xlUp))
If C.Offset(, -1) <> "" Then
Res = C.Offset(, -1)
End If
If Not Dico.exists(Res & "***" & "***" & i + 1 & "***" & C.Value) Then
Dico.Add Res & "***" & i + 1 & "***" & C.Value, Res & "***" & i + 1 & "***" & C.Value
End If
Next C
End With
Next i
With Sheets("Feuil1")
For Each Item In Dico.items
Txt = Split(Item, "***")
lig = Application.Match(Txt(0), Tabl1, 0) - 1
Tabl2(lig, CInt(Txt(1)) - 1) = Tabl2(lig, CInt(Txt(1)) - 1) + 1
Next Item
For i = 0 To UBound(Tabl1)
lig = 0
For x = 0 To UBound(Tabl2, 2)
If Tabl2(i, x) > lig Then lig = Tabl2(i, x)
Next x
For x = 1 To lig
Ligne = Ligne + 1
.Cells(Ligne, 5) = Tabl1(i)
Next x
Next i
For Each Item In Dico.items
For i = 2 To .Cells(.Rows.Count, 5).End(xlUp).Row
Txt = Split(Item, "***")
If .Cells(i, 5) = Txt(0) And .Cells(i, 17).Offset(, CInt(Txt(1))) = "" Then
.Cells(i, 17).Offset(, CInt(Txt(1))) = Txt(2)
Exit For
End If
Next i
Next Item
With .[E1].CurrentRegion
.Borders.LineStyle = xlContinuous
.BorderAround xlContinuous, xlThin, xlColorIndexAutomatic
End With
With .[E1].CurrentRegion.Offset(, 13).Resize(, 3)
.Borders.LineStyle = xlContinuous
.BorderAround xlContinuous, xlThin, xlColorIndexAutomatic
End With
Res = "E2"
Application.DisplayAlerts = False
For i = 2 To .Cells(.Rows.Count, 5).End(xlUp).Row
If .Cells(i, 5) <> .Range(Res) Then
.Range(.Cells(i, 5).Offset(-1), Range(Res)).Merge
Res = .Cells(i, 5).Address
End If
If i = .Cells(.Rows.Count, 5).End(xlUp).Row Then
.Range(.Cells(i, 5), Range(Res)).Merge
End If
Next i
.Range(.[E2], .Cells(.Rows.Count, 5).End(xlUp)).VerticalAlignment = xlCenter
Application.DisplayAlerts = True
End With
End Sub |
Partager