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
| Function InitEndPeriodDays(Date1Row As Long) As Variant
Dim PeriodArr(6) As Range
Dim CpDates(2) As Variant
Dim Cellule As Range
Dim DeltaD As Integer, i As Integer, DayNo As Integer
Dim NextRow As Long
Dim Date1 As Date, Date2 As Date, TestDate
Date1 = Sheets("BD").Cells(Date1Row, 3)
CpDates(1) = Date1
Date2 = Sheets("BD").Cells(Date1Row, 4)
CpDates(2) = Date2
NextRow = Date1Row + 1
Do While Sheets("BD").Cells(Date1Row, 2) = Sheets("BD").Cells(NextRow, 2) And _
Sheets("BD").Cells(Date1Row, 5) = Sheets("BD").Cells(NextRow, 5)
Date1 = Sheets("BD").Cells(Date1Row, 4) ' Date de fin de la période
Date2 = Sheets("BD").Cells(NextRow, 3) ' Date de début de la période suivante
DeltaD = DateDiff("d", Date1, Date2) - 1
For i = 1 To DeltaD
TestDate = Date1 + i
DayNo = Weekday(TestDate)
If DayNo < 7 And DayNo > 1 Then ' le jour testé n'est pas un jour de we
' if not jour férié then exit do
End If
Next i
CpDates(2) = Sheets("BD").Cells(NextRow, 4)
Date1Row = NextRow
NextRow = NextRow + 1
Loop
InitEndPeriodDays = CpDates
End Function
Sub TestInitEndPeriodDays()
Dim TestArr() As Variant
ReDim TestArr(2)
TestArr = InitEndPeriodDays(540)
MsgBox TestArr(1) & Chr(10) & TestArr(2)
End Sub |
Partager