Sub Décomposer()
Dim t#, derlig&, dercol%, t1, colref%, t2(), i&, n&, j&, k%
t = Timer 'facultatif, pour chronométrer
' supprime l'onglet "Décomposé" s'il existe
Application.DisplayAlerts = False
If SH_exist("Décomposé") = True Then Sheets("Décomposé").Delete
Application.DisplayAlerts = True
' creation de l'onglet "Décomposé"
Sheets.Add after:=Sheets(Worksheets.Count)
Sheets(Worksheets.Count).Name = "Décomposé"
' reproduit le format de l'onglet "A COMPLETER"
Sheets("A COMPLETER").Select
colcredo = Application.Match("CREDO", Sheets("A COMPLETER").[2:2], 0)
Cells.Select
Range("a1").Activate
Selection.Copy
Sheets("Décomposé").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWindow.Zoom = 69
Sheets("A COMPLETER").Select
Rows("1:2").Select
Selection.Copy
Sheets("Décomposé").Select
Rows("1:1").Select
ActiveSheet.Paste
Columns(colcredo).NumberFormat = "@"
Columns(colcredo + 4).NumberFormat = "@"
Sheets("A COMPLETER").Select
With Sheets("A COMPLETER")
derlig = .Cells(.Rows.Count, 1).End(xlUp).Row
dercol = .Cells(2, .Columns.Count).End(xlToLeft).Column
t1 = .[A1].Resize(derlig, dercol)
colref = Application.Match("QUANTITE", .[2:2], 0)
End With
ReDim t2(1 To Sheets("Décomposé").Rows.Count - 1, 1 To dercol)
For i = 3 To derlig
n = Int(t1(i, colref) / 10)
For j = j + 1 To j + n
For k = 1 To dercol
t2(j, k) = t1(i, k)
Next
t2(j, colref) = 10
Next
If t1(i, colref) Mod 10 Then
For k = 1 To dercol
t2(j, k) = t1(i, k)
Next
t2(j, colref) = t1(i, colref) Mod 10
Else
j = j - 1
End If
Next
With Sheets("Décomposé")
If j Then .[A3].Resize(j, dercol) = t2
.[A3].Offset(j).Resize(.Rows.Count - j - 2, dercol).ClearContents
.Activate
End With
'mise en couleur
Application.ScreenUpdating = False
With Sheets("Décomposé")
derlig = .Cells(.Rows.Count, 1).End(xlUp).Row
dercol = .Cells(2, .Columns.Count).End(xlToLeft).Column
End With
Range("a3").Select
col = 3
For d = 3 To derlig
Set cel1 = Range("a" & d)
Range(cel1, ActiveCell.Offset(0, dercol - 1)).Interior.ColorIndex = col
Set cel2 = Range("a" & d + 1)
credo1 = cel1.Value
credo2 = cel2.Value
cel2.Select
If credo1 <> credo2 Then
col = col + 1
Range(cel2, ActiveCell.Offset(0, dercol - 1)).Interior.ColorIndex = col
Else
Range(cel2, ActiveCell.Offset(0, dercol - 1)).Interior.ColorIndex = col
End If
If col = 56 Then col = 3
Next d
Application.ScreenUpdating = True
MsgBox "Durée " & Format(Timer - t, "0.0 \s")
End Sub