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.

0 Answers