PDA

View Full Version : [SOLVED] Match Overlap Date Range and multiple criteria - Need to match extra criteria



JOEYSCLEE
08-28-2018, 08:16 AM
Hi, there

I have 2 tabs - Holiday and Result in the attachment and I got the code as below.

Nevertheless, Column A with header "Name" on holiday tab needs to match with country as well in order to find out holiday name on result tab.

Would you please help to advise how I can get the result - holiday name with the exact date on Result tab which falls into holiday.:help


Option Explicit
Function GetHoliday(Cntry As String, Date1 As Date, Dates As Range) As String
Dim vDates As Variant
Dim i As Long
Dim s As String

vDates = Intersect(Dates.Parent.UsedRange, Dates).Value
s = UCase(Cntry)


For i = LBound(vDates, 1) + 1 To UBound(vDates, 1)
vDates(i, 1) = UCase(vDates(i, 1))
If vDates(i, 1) = s Then
If vDates(i, 3) <= Date1 And Date1 <= vDates(i, 4) Then
GetHoliday = vDates(i, 2)
Exit Function
End If
End If
Next i

GetHoliday = "Not Holiday" End Function

p45cal
08-28-2018, 12:14 PM
No attachment.

JOEYSCLEE
08-28-2018, 07:04 PM
No attachment.

Thanks p45cal!! Here is the attachment

p45cal
08-29-2018, 02:58 AM
You've added a column, so the original function doesn't look at the right column in the table anymore! (cf. http://www.vbaexpress.com/forum/showthread.php?62809 ).
Change the function to:

Function GetHoliday(Cntry As String, Nme As String, Date1 As Date, Dates As Range) As String
Dim vDates As Variant
Dim i As Long
Dim s As String, t As String

vDates = Intersect(Dates.Parent.UsedRange, Dates).Value
s = UCase(Cntry)
t = UCase(Nme)

For i = LBound(vDates, 1) + 1 To UBound(vDates, 1)
If UCase(vDates(i, 1)) = t Then

If UCase(vDates(i, 2)) = s Then
If vDates(i, 4) <= Date1 And Date1 <= vDates(i, 5) Then
GetHoliday = vDates(i, 3)
Exit Function
End If
End If
End If
Next i

GetHoliday = "Not Holiday"
End Function

and call it in a cell, eg.:
=GetHoliday(D2,B2,E2,Holiday!$A$1:$E$22)

22790


I hope it'll be… 'workable'.

JOEYSCLEE
08-29-2018, 06:46 AM
Thanks p45cal...It's workable!!:clap: