Check the date from a column of a table

[amircse0711]

Please find the below code to understand my question:

Set tbl = Sheets("Employee Leave Tracker").ListObjects("LeaveTracker")

My table "LeaveTracker" has start date in column 2 and end date in column 3. I want to check, if the user input (through calender - DTpicker) falls with the range of the date in col 2&3.

 

[pbornemeier]

Assuming EmpID is in column A of the table.

Option Explicit

Sub Test_CheckDates()

    Dim sEmpID As String
    Dim dteInput As Date
    Dim retVal As Variant
    Dim sOutput As String

    sEmpID = "A5"
    dteInput = #7/1/2019#
    
    retVal = CheckDates(sEmpID, dteInput)
    
    If retVal(0) Then
        sOutput = "Date In Range"
    Else
        sOutput = retVal(1)
    End If
            
    MsgBox "Emp ID: " & sEmpID & vbLf & _
        "Date: " & dteInput & vbLf & vbLf & _
        sOutput, , "Check Date"
    
End Sub

Function CheckDates(sEmpID As String, dteInput As Date)

    Dim tbl As ListObject
    Dim oFound As Object
    
    Set tbl = Sheets("Employee Leave Tracker").ListObjects("LeaveTracker")
    
    With tbl.DataBodyRange.Columns(1)
        Set oFound = .Find(What:=sEmpID, LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)
        If Not oFound Is Nothing Then
            'EmpID found
            If dteInput >= oFound.Offset(0, 1) And dteInput <= oFound.Offset(0, 2) Then
                'In range
                CheckDates = Array(True, "Date In Range")
            Else
                'Not in range
                CheckDates = Array(False, "Date Not in Range")
            End If
            
        Else
            'EmpID not found
            CheckDates = Array(False, "EmpID not Found")
        End If
    
    End With
End Function