Expand array of dates based on 2 criteria (VBA)

[Retroshift]

Hi
I have a counter with a minimum value of 17. As long as this value is not reached, then extra dates should be added to an array of dates.
The extra dates should be workdays: if for example 2 extra dates are needed to reach the 17 value, but the 24th of December is not a workday, yet 23rd and 22nd are a workday, then 23rd and 22nd should be added to the array and not 24th. And so on.
How can I alter the draft VBA code accordingly?

Year = ThisWorkbook.Worksheets("Holidays").Range("C4")

If counter <= 16 And Weekday("24/12/" & Year, 2) < 6 Then Redim Arr() to include date ("24/12/" & Year) in an array of dates
If counter <= 15 And Weekday("23/12/" & Year, 2) < 6 Then Redim Arr() to include date ("23/12/" & Year) in an array of dates
If counter <= 14 And Weekday("22/12/" & Year, 2) < 6 Then Redim Arr() to include date ("22/12/" & Year) in an array of dates
If counter = 13 And Weekday("21/12/" & Year, 2) < 6 Then Redim Arr() to include date ("21/12/" & Year) in an array of dates

 

[Herakles]

So are you trying to create an array of x number of working days prior to and including 24/12/**** ?

 

[Retroshift]

There is an array of holiday dates "arr(1 To 21)". The days 24, 23, 22, 21 of December are not (yet) included in this array.

If the counter is lower than 17, up to 4 days can be added to the array (24, 23, 22, 21 of December) depending on the criteria whether these days are working days or not (e.g. if the counter is 15 then the 24th and 23rd of December are added to the holiday dates array, given that both of them are working days (and thus not weekend days)).

 

[Retroshift]

Anyone?

For the first date it could be something like below, but I am not sure how to proceed with the other dates:

V = 21
If counter <= 16 And Weekday("24/12/" & Year, 2) < 6 Then
ReDim Preserve arr(1 To V + 1)
arr(UBound(arr)) = "24/12/" & Year 'adds new date to array
End If

 

[Retroshift]

Can anyone help me out with the following code, please? In the year 2027, the counter is 15, and yet the working day of 23/12/2027 is not included in the array "arr". How should the vba code be rewritten to include this date in the array? The code should also work for the year 2033 (with counter 16) where 23/12/2033 is added to the array because it is a working day and 24/12/2033 is not.

V = 21

If counter = 13 And Weekday("21/12/2027", 2) < 6 Then
ReDim Preserve arr(1 To V + 1)
arr(UBound(arr)) = "21/12/2027" 'should add this date to array if counter is 13 and date is on a working day (current vba code does not seem to work)

ElseIf counter <= 14 And Weekday("22/12/2027", 2) < 6 Then
ReDim Preserve arr(1 To V + 1)
arr(UBound(arr)) = "22/12/2027" 'should add this date to array if counter is 14 or lower and date is on a working day (current vba code does not seem to work)

ElseIf counter <= 15 And Weekday("23/12/2027", 2) < 6 Then
ReDim Preserve arr(1 To V + 1)
arr(UBound(arr)) = "23/12/2027"      'should add this date to array but does not because of failing vba code (23/12/2027 meets the criterium of being a working day)

ElseIf counter <= 16 And Weekday("24/12/2027", 2) < 6 Then
ReDim Preserve arr(1 To V + 1)
arr(UBound(arr)) = "24/12/2027" 'adds new date to array
End If