Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup formula with date test
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/ ------------------------------------------------ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Logical Test on Date value | Excel Discussion (Misc queries) | |||
Convert test to date | Excel Discussion (Misc queries) | |||
test to Julian date | Excel Discussion (Misc queries) | |||
Test plus date and time | Excel Worksheet Functions | |||
test for date in column question | Excel Worksheet Functions |