Sub test() FillRangeWithOpenDates Range("A1"), #1/1/2010#, #12/31/2010# End Sub Private Sub FillRangeWithOpenDates(ByVal voTarget As Range, ByVal vdMin As Date, ByVal vdMax As Date) If Not Nothing Is voTarget Then Set voTarget = voTarget.Offset Do While vdMin <= vdMax If Not IsHoliday(vdMin) Then voTarget.Value = vdMin Set voTarget = voTarget.Offset(1) End If vdMin = vdMin + 1 Loop End If End Sub Public Function IsHoliday(ByVal vdInput As Date) As Boolean Dim nDelta As Long If Weekday(vdInput, vbMonday) >= 6 Then IsHoliday = True '# Ici, on considère que Samedi et Dimanche sont des jours non ouvrés... ElseIf InStr(1, "01/01 01/05 08/05 14/07 15/08 01/11 11/11 25/12", Format$(vdInput, "DD\/MM")) Then IsHoliday = True Else nDelta = DateDiff("D", Easter(Year(vdInput)), vdInput) IsHoliday (nDelta 0 Or _ nDelta = 1 Or _ nDelta = 39 Or _ nDelta = 49 Or _ nDelta = 50) End If End Function Public Property Get Easter(ByVal vnYear As Integer) As Date Dim nE As Integer Dim nH As Integer Dim nK As Integer Dim nP As Integer Dim nQ As Integer Dim nI As Integer Dim nJ As Integer Dim nGolden As Integer Dim nCentury As Integer Dim nCenturyQ As Integer nGolden = vnYear Mod 19 nCentury = vnYear \ 100 nCenturyQ = nCentury \ 4 nE = (8 * nCentury + 13) \ 25 nH = (19 * nGolden + nCentury - nCenturyQ - nE + 15) Mod 30 nK = nH \ 28 nP = 29 \ (nH + 1) nQ = (21 - nGolden) \ 11 nI = (nK * nP * nQ - 1) * nK + nH nJ = ((vnYear \ 4 + vnYear) + nI + 2 + nCenturyQ - nCentury) Mod 7 nJ = 28 + nI - nJ If nJ <= 31 Then Easter = DateSerial(vnYear, 3, nJ) Else Easter = DateSerial(vnYear, 4, nJ - 31) End If End Property
Private Sub CommandButton3_Click() Static toto As Date, titi As String datedeb = DateSerial(2010, 1, 27) datefin = DateSerial(2010, 2, 3) Do While datedeb < datefin titi = Application.WorksheetFunction.WorkDay(datedeb - 1, 1) If titi <> toto Then MsgBox Format(titi, "dddd dd/mm/yyyy"): toto = titi datedeb = datedeb + 1 Loop End Sub
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionPrivate Sub CommandButton3_Click() Dim toto As Date, titi As Date, ou As Integer datedeb = DateSerial(2010, 1, 27) ' <<=============ici la date de début datefin = DateSerial(2010, 2, 10) ' <<<<<<<=========ici lka date de fin Dim zut As Range Set zut = Range("C5:C" & datefin - datedeb + ou) ' <<<<<<<<=======on veut afficher à partir de la cellule C5 (exemple) ou = zut.Row Do While datedeb < datefin titi = Application.WorksheetFunction.WorkDay(datedeb - 1, 1) If titi <> toto Then zut.Cells(ou - zut.Row + 1, 1).Value titi: ou ou + 1: toto = titi datedeb = datedeb + 1 Loop End Sub