Option Explicit
Sub RecapParPhase_v2()
Dim DicTit As Dictionary, CMax As Long, L As Long, C As Long, TRés(1 To 500, 1 To 12), _
Dpt As SsGr, Serv As SsGr, Détail
'Recherche les entetes des colonnes pour identifier le numero de la colonne.
Dim nC_PHASE&, nC_LibEQP&, nc_ETAT&, nC_BUD&, nC_ENG&, nC_FAC&
Dim LOt As ListObject
Set LOt = FDonn.ListObjects(1)
nC_PHASE = NColTab(LOt, "PHASE")
nC_LibEQP = NColTab(LOt, "Lib EQP")
nc_ETAT = NColTab(LOt, "ETAT")
nC_BUD = NColTab(LOt, "BUD")
nC_ENG = NColTab(LOt, "ENG")
nC_FAC = NColTab(LOt, "FAC")
Set DicTit = GigIdx.DicInvent(LOt, "PHASE", 4): CMax = 3 + DicTit.Count
L = 1
For C = 1 To 3: TRés(L, C) = Choose(C, "DPT", "SERV", "BILAN"): Next C
VerserTitres TRés, DicTit
For Each Dpt In GigIdx.Gigogne(Null, "DPT", "SERV")
For Each Serv In Dpt.Co
L = L + 1
TRés(L, 1) = Dpt.ID
TRés(L, 2) = Serv.ID
TRés(L, 3) = "BUD"
TRés(L + 1, 3) = "ENG"
TRés(L + 2, 3) = "FAC"
For Each Détail In Serv.Co
C = DicTit(Détail(nC_PHASE))
TRés(L, C) = TRés(L, C) + Détail(nC_BUD)
TRés(L + 1, C) = TRés(L + 1, C) + Détail(nC_ENG)
TRés(L + 2, C) = TRés(L + 2, C) + Détail(nC_FAC)
Next Détail
L = L + 3
TRés(L, 1) = "Reste à engager (DUB-ENG)"
For C = 4 To CMax: TRés(L, C) = TRés(L - 3, C) - TRés(L - 2, C)
Next C
L = L + 1
TRés(L, 1) = "%(ENG/BUD)"
For C = 4 To CMax: If TRés(L - 4, C) <> 0 Then TRés(L, C) = TRés(L - 3, C) / TRés(L - 4, C)
Next C
L = L + 1
Next Serv, Dpt
FDonn.[A15].Resize(500, 12).Value = TRés
End Sub
Function NColTab(ByVal LOt As ListObject, ByVal Titre As String) As Long
On Error Resume Next
NColTab = LOt.ListColumns(Titre).Index
If Err Then MsgBox "Le tableau " & LOt.Name & " ne contient pas de colonne """ _
& Titre & """.", vbCritical: End
End Function