VBA for a date in a holiday list not working right

[Lightkeepr]

I have the following VBA code that the first part where it looks to see if the date is a weekend date works great. It adjusts the dates backwards to the next available Mon-Fri date. The second step is for it to check the date against a list of dates that are holidays. This step doesn't seem to be doing anything and I cannot figure out why. I have it looping three time so that it checks to make sure that the new adjusted date (if one was found on the list) is not also a holiday on the list. Any ideas of where I'm missing something? In the worksheet that I am using this, column A has the ideal date for a delivery, in column D I have the formula =DateChecker(A5) to give me the adjusted date based on weekends/holidays off.

Function DateChecker(ByVal dateIO As Date) As Date
    Dim SearchRng As Range
    Dim DateSearch As String
    Dim resMatch As Variant
    Dim LoopCheck As Integer
'    Application.Volatile True

    Set SearchRng = ThisWorkbook.Worksheets("Holidays").Range("C5:C55")

'Check if date is weekend and adjust it
    If Weekday(dateIO, 2) = 7 Then
        dateIO = dateIO - 2
    ElseIf Weekday(dateIO, 2) = 6 Then
        dateIO = dateIO - 1
    End If

    LoopCheck = 3
    
'check date against the holidays list
    For LoopCheck = 1 To LoopCheck
        DateSearch = dateIO
        resMatch = Application.Match(DateSearch, SearchRng, 0)
        
        If IsError(resMatch) Then
            GoTo Last
        Else
            dateIO = dateIO - 1
        End If
    Next LoopCheck

Last:
'Check if final date is weekend and adjust it
    If Weekday(dateIO, 2) = 7 Then
        dateIO = dateIO - 2
    ElseIf Weekday(dateIO, 2) = 6 Then
        dateIO = dateIO - 1
    End If

    DateChecker = dateIO

End Function

 

[Lightkeepr]

I see the conundrum now. I think you can extend the formula to check another level to get it to work, but then what happens if you have 3 in a row, etc.
So here is a simple User Defined Function that should do what you want:

Function MyDateChecker(dtInput As Date) As Date

    Dim dt1 As Date
    Dim rngHol As Range
   
'   Set initial values
    dt1 = dtInput
    Set rngHol = ThisWorkbook.Worksheets("Holidays").Range("C5:C55")
   
'   Loop through days backwards until you find a valid weekday that is not a holiday
    Do
        If (Weekday(dt1, 2) < 6) And Application.WorksheetFunction.CountIf(rngHol, dt1) = 0 Then
            MyDateChecker = dt1
            Exit Do
        Else
            dt1 = dt1 - 1
        End If
    Loop

End Function

Basically, we just loop and keep subtracting one from the date until we get to a valid weekday that is not a holiday.

Not only did I learn a more simple way of looking at the way to attack the problem, I also learned about 'Exit Do' as a way to get out of the Do Loop. You learn something new every day...

Thank you for your help with this, I greatly appreciate it!

 

[Joe4]

You are welcome!
Glad I was able to help!