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 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97
|
Sub AjouterColonneTotalAttritionCumule(ByVal FeuilleTcd As Worksheet, ByVal Tcd As PivotTable)
Dim ColTotalVentiles As Long
Dim ColTotalCessations As Long
Dim ColAttrition As Long
Dim LigneDeTitre As Long
Dim DerniereLigne As Long
Dim CelluleTcd As Range
Dim AireTotalAttrition As Range
Application.ScreenUpdating = False
With Tcd
ColTotalVentiles = 0
ColTotalCessations = 0
ColAttrition = .TableRange2.Column + .TableRange2.Columns.Count
LigneDeTitre = .RowRange.Row
DerniereLigne = .RowRange.Row + .RowRange.Rows.Count - 2
For Each CelluleTcd In .ColumnRange
Select Case CelluleTcd
Case "total ventilés en cumulé"
ColTotalVentiles = CelluleTcd.Column
Case "total cessation en cumulé"
ColTotalCessations = CelluleTcd.Column
End Select
Next CelluleTcd
If ColTotalVentiles > 0 And ColTotalCessations > 0 Then
With FeuilleTcd
Set AireTotalAttrition = .Range(.Cells(LigneDeTitre + 1, ColAttrition), .Cells(DerniereLigne, ColAttrition))
'Effacement de l'ancienne colonne
Range(AireTotalAttrition.Offset(-2, 0), AireTotalAttrition.Offset(12, 0)).Clear
' Calcul du total attrition en cumulé
For Each CelluleTcd In AireTotalAttrition
If CelluleTcd.Offset(0, ColTotalVentiles - ColAttrition) > 0 Then
With CelluleTcd
.Value = CelluleTcd.Offset(0, ColTotalCessations - ColAttrition) / CelluleTcd.Offset(0, ColTotalVentiles - ColAttrition)
.NumberFormat = "0.00%"
End With
End If
Next CelluleTcd
' Mise en forme des cellules de titre
With Range(AireTotalAttrition.Cells(1, 1).Offset(-2, 0), AireTotalAttrition.Cells(1, 1).Offset(-1, 0))
.Interior.Color = RGB(220, 230, 241)
.Font.Bold = True
.HorizontalAlignment = xlCenter
End With
' Création de la bordure basse de la cellule de titre
With AireTotalAttrition.Cells(1, 1).Offset(-1, 0)
.Value = "total attrition en cumulé"
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ThemeColor = 5
.TintAndShade = 0.599963377788629
.Weight = xlThin
End With
End With
' Mise en forme de la cellule total
With .Cells(AireTotalAttrition.Row + AireTotalAttrition.Rows.Count, AireTotalAttrition.Column)
.Interior.Color = RGB(220, 230, 241)
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ThemeColor = 5
.TintAndShade = 0.599963377788629
.Weight = xlThin
End With
End With
Set AireTotalAttrition = Nothing
End With
' MsgBox .TableRange2.Address & ", " & .DataBodyRange.Address & Chr(10) _
' & "Colonne attrition" & ColAttrition & Chr(10) _
' & "Ligne de titre" & LigneDeTitre
End If
End With
Application.ScreenUpdating = True
End Sub |
Partager