ExcelBanter - Lookup formula with date test

I think that something like should work. But it didn't work for me. I've
always had trouble in VBA with dates.

Option Explicit
Sub testm01()

Dim res As Variant
Dim myMonth As Variant
Dim mydate As Date

myMonth = Array(DateSerial(2003, 1, 1), DateSerial(2003, 9, 9))

res = Application.Match(DateSerial(2003, 9, 9), myMonth, 0)

If IsError(res) Then
MsgBox "error"
Else
MsgBox "ok"
End If

End Sub

But if you can convert your dates to longs, the same kind of thing works ok:

Option Explicit
Sub testme03()

Dim myArr(1 To 12) As Long
Dim mydate As Long
Dim iCtr As Long
Dim res As Variant

'just test data
For iCtr = lbound(myArr) to ubound(myArr)
myArr(iCtr) = CLng(DateSerial(2003, iCtr, 3))
Next iCtr
mydate = CLng(DateSerial(2003, 8, 3))

'real stuff here

res = Application.Match(mydate, myArr, 0)
If IsError(res) Then
MsgBox Format(mydate, "mm/dd/yyyy") & " wasn't found"
Else
MsgBox Format(mydate, "mm/dd/yyyy") & " was found as element: " & res
End If

End Sub

jethawk wrote:

I have a situation in which I need to find a date in an array of dates.
For example, for the date value
9/9/03

in the array:

Month

------------------------------------------------
Message posted from http://www.ExcelForum.com/

-- View and post Excel related usenet messages directly from http://www.ExcelForum.com
-- Hundreds of free MS Excel tips, tricks and solutions at http://www.ExcelTip.com/
------------------------------------------------

--

Dave Peterson