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
| Option Explicit
Private Type TITRES
TEXTE As String
COULEUR As Long
End Type
Sub Chapitres()
Const PREF As String = "CHAPITRE "
Dim LastLig As Long, i As Long, Cpt As Long
Dim MesTitres As TITRES
Dim N As Byte
Application.ScreenUpdating = False
With Worksheets("Feuil1") 'à adapter
LastLig = .Cells(.Rows.Count, 1).End(xlUp).Row
For i = LastLig To 2 Step -1
If Not .Cells(i, 1) Like PREF & "*" And Not .Cells(i - 1, 1) Like PREF & "*" Then
N = Int(Val(.Cells(i, 1)) / 1000)
If N <> 0 Then
If Int(Val(.Cells(i - 1, 1)) / 1000) <> N Then
MesTitres = TitreChapitres(N)
.Rows(i).Insert
With .Range("A" & i)
.Value = UCase(PREF & N & "_" & MesTitres.TEXTE)
.Resize(, 6).Merge
With .Font
.Bold = True
.Size = 13
End With
.Offset(Cpt + 2).EntireRow.Insert
.Offset(1).Resize(Cpt + 1).Interior.ColorIndex = MesTitres.COULEUR
End With
With .Range("E" & i + Cpt + 2).Resize(, 2)
.Merge
.Formula = "=""TOTAL: ""&SUM(F" & i + 1 & ":F" & i + 1 + Cpt & ")"
With .Font
.Bold = True
.Size = 12
End With
With .Borders
.LineStyle = xlContinuous
.Weight = xlMedium
End With
End With
.Range("F" & i + 1 & ":F" & i + 1 + Cpt).FormulaR1C1 = "=RC[-2]*RC[-1]"
Cpt = 0
Else
Cpt = Cpt + 1
End If
End If
End If
Next i
End With
End Sub
Private Function TitreChapitres(ByVal k As Byte) As TITRES
Dim TbTitres, TbCouleurs
TbTitres = Array("CHAP1", "CHAP2", "CHAP3", "CHAP4", "CHAP5", "CHAP6", "CHAP7", "CHAP8", "CHAP9") 'Adapte le texte des titres
TbCouleurs = Array(44, 5, 12, 27, 14, 34, 22, 41, 17) 'Adapte les index de couleur des groupes
TitreChapitres.TEXTE = TbTitres(k - 1)
TitreChapitres.COULEUR = TbCouleurs(k - 1)
End Function |