Bonjour à tous,
Un essai ...
remplace Private Sub Taux()
par ceci ...
Private Sub Taux() 'Calcul de taux d'affectation
If Not Range("A10:A200").Find("Taux d'affectation", , , , xlByRows) Is Nothing Then rep = MsgBox("Mise en Forme déjà effectuée", vbCritical, "ATTENTION"): Exit Sub
'Range("B:B").Columns.Insert
Application.ScreenUpdating = False
DebCol = Range("A5:AB5").Find("01", , , xlPart, xlByColumns).column
CD = Chr(DebCol + 64)
TextLig = Cells(Range("A1500").End(xlUp).Row, 1)
ActLig = Cells(Range("A1500").End(xlUp).Row, 1).Row
NbEqp = 0: [Z1] = 0
Do While NbEqp = 0 And (ActLig + J) > 8
NbEqp = InStr(1, Cells(ActLig + J, 1), ":")
J = J - 1
If NbEqp > 0 Then [Z1] = [Z1] + 1: NbEqp = 0
Loop
'Equipe = LTrim(Right(Range("A" & ActLig + J), 2))
'[Z1] = CInt(Equipe):
Plage = "A1:A1500": [B1] = 1
For T = 1 To [Z1]
With ActiveSheet.Range(Plage)
Set LT = .Find(TextLig, , , , xlByRows)
If Not LT Is Nothing Then
Lin = LT.Row
End If
End With
Plage = "A" & Lin & ":A1500"
Cells(1, T + 2) = Lin
Next T
'***
PLig = Cells(1, T + 1)
Do Until Range("A" & PLig) Like "Planifiés"
PLig = PLig - 1
Loop
' "=R[Var]C[]/R[Var+2]C[]"
Col = 1: Var = "=IFERROR(R[" & (PLig - Lin - 1) & "]C[]/R[" & (PLig - Lin + 1) & "]C[],"""")"
DerJour = Left(Right(Range("A2"), 10), 2)
Col = Range("D5:AK5").Find(DerJour, , , xlPart, xlByColumns).column
For lig = 1 To Lin + ([Z1] - 1)
If Range("A" & lig) = TextLig Then
Rows(CStr(lig + 1) & ":" & lig + 1).Select
Selection.Insert Shift:=xlDown
Range(CD & [C1] - 3).Copy Range(Cells(lig + 1, DebCol), Cells(lig + 1, Col))
Application.CutCopyMode = False
Range("BZ" & lig + 1).FormulaR1C1 = Var
Range("BZ" & lig + 1).NumberFormat = "0.0%"
Range(CD & lig + 1).FormulaR1C1 = Var
Range(CD & lig + 1).NumberFormat = "0.0%"
Range(Cells(lig + 1, DebCol), Cells(lig + 1, Col)).FormulaR1C1 = Range(CD & lig + 1).FormulaR1C1: _
Range(Cells(lig + 1, 4), Cells(lig + 1, Col)).NumberFormat = "0.0%"
Range(Cells(lig, 1), Cells(lig, 2)).Copy Range("A" & lig + 1)
Application.CutCopyMode = False
Range("A" & lig + 1) = "Taux d'affectation"
If Left(Cells(lig + 1, "A"), 18) = "Taux d'affectation" Then
With Range(Cells(lig + 1, "C"), Cells(lig + 1, "AY")).Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Color = RGB(89, 164, 219)
.TintAndShade = 0
.Weight = xlThin
End With
End If
End If
Next lig ' ici -------4
Range("A" & Lin + 2).Select: [A1] = PLig: '[D1] = Lin
TotalTab
End Sub
ric