Dates in an Array Constant for comparison

[WassimN]

Hello All,

I am drawing a blank. I need an example of using an array constant with a set of 10 days. I will use the array constant to determine if a date matches any of the dates in the array constant.

This is part of an application that I am helping write for a non-profit organization so that they can synchronize their mailing applications.

Should I use the dates as text, and what if they change year after year?! Or should I use the ordinal number of the dates in the array constant?

An example would be much appreciated.

 

[Domenic]

Try something like this...

Option Explicit

Sub test()


    'declare variables
    Dim vMatchVal As Variant
    Dim vDates As Variant
    Dim dtDate As Date
    
    'assign array of date literals to a variant variable (change and add dates as desired)
    vDates = Array(#3/15/2018#, #8/29/2018#, #11/21/2018#) 
    
    'assign the lookup date to a date variable
    dtDate = Range("A2").Value
    
    'check whether the date matches a date within the array
    vMatchVal = Application.Match(dtDate, vDates, 0)
    If Not IsError(vMatchVal) Then
        MsgBox "The date matches...", vbInformation
    Else
        MsgBox "The date doesn't match...", vbInformation
    End If
    
End Sub

Hope this helps!

 

[WassimN]

Thank You Domenic,

Sorry for the confusion, I was hoping for a formula in a cell on a worksheet version. I will keep the code for later.

So far I have it working as a formula referring to a range of cells with the dates in question in each cell on a worksheet. It would be easy to manage in the future, but I am still hoping for a worksheet formula using an array constant.

 

[Domenic]

In that case, assuming that A2 contains the lookup date, try...

=MATCH(A2,{"1/15/2018","2/15/2018","3/15/2018"}+0,0)

Change the dates accordingly.

Hope this helps!