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
| Option Explicit
Sub Remplissage()
Dim LastLig As Long, i As Long, j As Long, k As Long, m As Long
Dim n As Integer
Dim Tb, Res()
Application.ScreenUpdating = False
With Worksheets("MaFeuille") 'A adapter
LastLig = .Cells(.Rows.Count, 1).End(xlUp).Row
Tb = .Range("A2:B" & LastLig)
ReDim Res(1 To 2, 1 To 1)
Res(1, 1) = CLng(Tb(1, 1))
Res(2, 1) = Tb(1, 2)
j = 1
For i = 2 To LastLig - 1
n = Diff(Tb, i)
m = j + n
ReDim Preserve Res(1 To 2, 1 To m)
For k = j + 1 To m
Res(1, k) = Suiv(Res(1, k - 1))
Res(2, k) = IIf(n = 1 Or k = m, Tb(i, 2), Tb(i - 1, 2))
Next k
j = m
Next i
With .Range("D2")
.Resize(j, 2) = Application.Transpose(Res)
.Resize(j, 1).NumberFormat = "dd/mm/yyyy"
End With
End With
End Sub
Private Function Diff(ByVal T, ByVal d As Long) As Byte
Dim Dte As Long, Der As Long
Der = CLng(T(d - 1, 1))
Dte = CLng(T(d, 1))
Diff = Evaluate("=NETWORKDAYS(" & Der & "," & Dte & ")") - 1
End Function
Private Function Suiv(ByVal Dte As Long) As Long
Suiv = Evaluate("=WORKDAY(" & Dte & ",1)")
End Function |
Partager