Lookup if a date falls between two dates stored in a range

Domhnall Ó Dubhlainn 0
Viewed 42

There are many similar sounding questions to this but they either seem to be about Excel formulas (instead of actual VBA code) or they just don't fit my problem.

I have a named range in my sheet which lists a few time periods along with their respective start and end dates:

Periods range in Excel

I've named the range from M6 to O19 as "Periods" (the named range excludes the headers).

I need to have a VBA function which will check for the first Period for which a given date is greater than "Start" and less than "End".

Again, I have trawled through dozens of questions on SO but honestly nothing helped me. I've tried to adapt multiple answers to my problem but I'm still now left with an empty sub:

Public Function LookupNTAPeriod(lookupDate As Date) As String



End Function
vba excel
1 Answers

Please, use the next function:

Public Function LookupNTAPeriod(lookupDate As Date) As String
    Dim arr, i As Long
    Const rngName = "myName" 'please, use here your named range real name
    arr = Range(rngName).Value 'place the range in an array for faster iteration/processing
    For i = 1 To UBound(arr)
        If lookupDate >= CDate(arr(i, 2)) And lookupDate <= CDate(arr(i, 3)) Then
            LookupNTAPeriod = arr(i, 1): Exit Function
        End If
    Next i
    LookupNTAPeriod = "No match found..."
End Function

It can be tested in the next way:

Sub testLookupNTAPeriod()
    Dim lkDate As Date
    lkDate = Date
   
    Debug.Print LookupNTAPeriod(lkDate)
End Sub

It returns the matching "Period" from the named range first column

FaneDuru0
Related