Exclude Public Holidays

[Nathan95]

Hey All I have the bellow code which inputs all the dates within a date range that is provided through a user form which excludes weekends. Was just also looking to exclude any public holidays from a given list in a diffrent sheet called "Settings"

Any help would be appreciated.

Private Sub cbInputleave_Click()
Dim i As Long, k As Long, arr()
Dim FirstDate As Date, LastDate As Date
Dim rBlanks As Range
Dim ssheet As Worksheet
Dim ctl As Control
Dim Index As Long

Set ssheet = ThisWorkbook.Sheets("Mark Leave")
FirstDate = tbstartdate.Value
LastDate = tbenddate.Value

ReDim arr(1 To LastDate - FirstDate + 1, 1 To 1)
For i = FirstDate To LastDate
If Weekday(i, 2) < 6 Then
k = k + 1
arr(k, 1) = i
End If
Next

 

[Phuoc]

Try this

If Weekday(i, 2) < 6 And WorksheetFunction.CountIf(Sheets("Settings").Range("A2:A100"), i) = 0 Then
    k = k + 1
    arr(k, 1) = i
End If

 

[Nathan95]

Try this

If Weekday(i, 2) < 6 And WorksheetFunction.CountIf(Sheets("Settings").Range("A2:A100"), i) = 0 Then
    k = k + 1
    arr(k, 1) = i
End If

Perfect thanks for that!