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
| Sub Stat2DTab()
Set f = Sheets("BD")
TblBD = f.Range("A2:C" & f.[A65000].End(xlUp).Row).Value ' Array pour rapidité
colCrit1 = 1: colCrit2 = 3: colOper = 2
Set AdrResult = f.Range("f1") ' Adresse résultat
Set d1 = CreateObject("Scripting.Dictionary") ' Dictionnaire index pour rapidité
Set d2 = CreateObject("Scripting.Dictionary")
Dim TblRes(1 To 100, 1 To 100)
For i = LBound(TblBD) To UBound(TblBD)
clé1 = TblBD(i, colCrit1): If d1.exists(clé1) Then lig = d1(clé1) Else d1(clé1) = d1.Count + 1: lig = d1.Count
clé2 = TblBD(i, colCrit2): If d2.exists(clé2) Then col = d2(clé2) Else d2(clé2) = d2.Count + 1: col = d2.Count
TblRes(lig, col) = TblBD(i, colOper)
Next i
AdrResult.Offset(1).Resize(d1.Count, 1) = Application.Transpose(d1.keys) ' titre lignes
AdrResult.Offset(, 1).Resize(1, d2.Count) = d2.keys ' titres colonnes
AdrResult.Offset(1, 1).Resize(d1.Count, d2.Count) = TblRes ' résultat
'-- coloriage titres
AdrResult.Offset(1).Resize(d1.Count).Interior.Color = vbBlack
AdrResult.Offset(1).Resize(d1.Count).Font.Color = vbWhite
AdrResult.Offset(, 1).Resize(, d2.Count).Interior.Color = vbBlack
AdrResult.Offset(, 1).Resize(, d2.Count).Font.Color = vbWhite
'--- tri
Set Rng = AdrResult.Resize(d1.Count + 1, d2.Count + 1) ' tri lignes & colonnes
Rng.Offset(1).Resize(Rng.Rows.Count - 1, Rng.Columns.Count).Sort key1:=Rng.Cells(2, 1), _
Order1:=xlAscending, Header:=xlNo, MatchCase:=False, Orientation:=xlSortColumns
Rng.Offset(, 1).Resize(Rng.Rows.Count, Rng.Columns.Count - 1).Sort key1:=Rng.Cells(1, 2), _
Order1:=xlAscending, Header:=xlNo, MatchCase:=False, Orientation:=xlSortRows
End Sub |
Partager