Sub Décomposé()
Dim t#, derlig&, dercol%, t1, colref%, t2(), i&, n&, a, j&, k%
t = Timer 'facultatif, pour chronométrer
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)
a = Application.Index(t1, i, 0) 'tableau mémorisé
For j = j + 1 To j + n
For k = 1 To dercol
t2(j, k) = a(k)
Next
t2(j, colref) = 10
Next
For k = 1 To dercol
t2(j, k) = a(k)
Next
t2(j, colref) = t1(i, colref) - 10 * n
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
MsgBox "Durée " & Format(Timer - t, "0.0 \s")
End Sub