Dim Onglet As String
Function JourB11(Jour As String) As Byte
Dim i As Byte
Jours = Array("lun", "mar", "mer", "jeu", "ven", "sam", "dim")
For i = 0 To 6
If Jours(i) = Jour Then Exit For
Next i
JourB11 = i + 1
End Function
Sub Ajouter_Moyenne()
Dim Jour As String
Dim Deb As Byte, Lig As Byte
Dim i As Integer
Dim MoisEn As Byte
Dim Mois_1 As Byte
Dim MaFormule As String
'Avril par défaut en partant du mois de mai
St = ActiveSheet.Name
Jour = LCase(Left(CStr(Range("B11")), 3))
'MoisEn : mois en cours
MoisEn = 7 - JourB11(Jour)
' Mois_1 : mois précédent
Mois_1 = 5 - MoisEn
Deb = 11 + MoisEn
With Sheets(Onglet)
Lig = .Range("B1", .Range("B65535").End(xlUp)).Rows.Count
End With
MaFormule = "=average(" & Onglet & "!D41:D" & Lig - Mois_1 & "," & "D11:D" & Deb - 1 & ")"
Range("D" & Deb).Formula = MaFormule
For i = 1 To 3
Lig = 7 * i + Deb
Range("D" & Lig).FormulaR1C1 = _
"=IF(ISERR(AVERAGE(R[-6]C:R[-1]C)),"""",AVERAGE(R[-6]C:R[-1]C))"
Range("D" & Lig).AutoFill Destination:=Range("D" & Lig & ":L" & Lig), Type:=xlFillCopy
Range("L" & Lig).Copy Destination:=Range("O" & Lig)
Application.CutCopyMode = False
Next i
Lig = 28 + Deb
If LCase(Left(Range("B" & Lig), 3)) = "dim" Then
Range("D" & Lig).FormulaR1C1 = _
"=IF(ISERR(AVERAGE(R[-6]C:R[-1]C)),"""",AVERAGE(R[-6]C:R[-1]C))"
Range("D" & Lig).AutoFill Destination:=Range("D" & Lig & ":L" & Lig), Type:=xlFillCopy
Range("L" & Lig).Copy Destination:=Range("O" & Lig)
Application.CutCopyMode = False
End If
End Sub
Sub AjouterFeuille()
Application.ScreenUpdating = False
Onglet = ActiveSheet.Name
Sheets(Worksheets.Count).Copy After:=Sheets(Worksheets.Count)
Range("A50") = Year(Range("a50")) & "/" & Month(Range("a50")) + 1 & "/" & 1
ActiveSheet.Name = Format([a50], "mmmm")
Range("D5:M35,O5:O35") = clearcontent
Range("M11:M35").FormulaR1C1 = _
"=RC[-9]+RC[-8]+RC[-7]+RC[-6]+RC[-5]+(RC[-4]*REFERENTIEL!R2C2)+(RC[-3]*REFERENTIEL!R3C2)+(RC[-2]*REFERENTIEL!R4C2)+(RC[-1]*REFERENTIEL!R5C2)"
Range("N11:N35").FormulaR1C1 = _
"=IF(R1C5="""","""",IF(RC[-1]="""","""",100%+(RC[-1]-LOOKUP(février!R1C5,REFERENTIEL!R3C4:R9C4,REFERENTIEL!R3C5:R9C5))/LOOKUP(février!R1C5,REFERENTIEL!R3C4:R9C4,REFERENTIEL!R3C5:R9C5)))"
Range("P11:P35").FormulaR1C1 = _
"=IF(RC[-1]="""","""",100%+(RC[-1]-LOOKUP(février!R1C5,REFERENTIEL!R3C4:R9C4,REFERENTIEL!R3C6:R9C6))/LOOKUP(février!R1C5,REFERENTIEL!R3C4:R9C4,REFERENTIEL!R3C6:R9C6))"
Range("Q11:Q35").FormulaR1C1 = "=RC[-4]+RC[-2]"
Ajouter_Moyenne
Application.ScreenUpdating = True
End Sub