Results 1 to 2 of 2

Thread: Check the date from a column of a table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Location
    France
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Check the date from a column of a table

    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.

  2. #2
    Board Regular pbornemeier's Avatar
    Join Date
    May 2005
    Location
    Virginia Beach, VA USA
    Posts
    3,682
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Check the date from a column of a table

    Assuming EmpID is in column A of the table.

    Code:
    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
    Phil

    - Use CODE tags to keep your code formatted. See: BB Tags
    - How to attach Screenshots
    - Try searching for your answer first, see how
    - Test and validate results for all code on a copy of your worksheet!! How do you use the code you just found?
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •