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
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