Using Excel VBA, I'm trying to check if a date is a Bank Holiday (or not). However unless the date is a Bank Holiday which is number 1 in my Bank Holiday array, I keep getting the #N/A.(or in the case below, the "not found" message) The ONLY time it matches is if the date is the first listed in my Bank Holiday array. I've looked at all the "Vlookup - #N/A queries which have been logged , but can't see an answer to my particular problem. I can use a For-Next loop using x= 1 to 9, to loop through Bank_Holiday, and it works perfectly. Why doesn't Vlookup work?
Dim Bank_Holiday(1 To 9) As Long Dim thisdate As Long Dim found As Boolean Dim nextdate As String Dim y As Variant
'populate the Bank Holiday dates (2020) as numbers
Bank_Holiday(1) = 43833 Bank_Holiday(2) = 43931 Bank_Holiday(3) = 43934 Bank_Holiday(4) = 43955 Bank_Holiday(5) = 43976 Bank_Holiday(6) = 44074 Bank_Holiday(7) = 44190 Bank_Holiday(8) = 44193 Bank_Holiday(9) = 44197 Windows("bankacct20 - Copy.xlsx").Activate ' An available workbook Sheets("Jan_Mar20").Select 'An available worksheet ActiveSheet.Cells(5, 1).Select 'Top of the "Date" column thisdate = ActiveCell() 'pick up the date y = Application.VLookup(thisdate, Bank_Holiday, 1, False) If Not (IsError(y)) Then found = True End If nextdate = Format(thisdate, "dddd dd mm yyyy") If found Then MsgBox ("Congratulations! " & nextdate & " Found!") Else MsgBox ("The Bank Holiday date " & nextdate & " does not exist") End If
I expect that when the active cell IS a bank holiday, the message box would reflect that.