It blew up in my tests this time. I was surprised, too.



Tom Ogilvy wrote:

I've never had trouble with this:

res = .Match(CLng(Date), .Index(myArr, 0, 1), 0)

I am surprised.

--
Regards,
Tom Ogilvy

Dave Peterson wrote in message
...
Sometimes I have trouble working with dates in routines like this.

I like to convert the dates to longs and then keep them in the array. If
you
can do this, then this is one way to retrieve the match:

Option Explicit
Sub testme()

Dim myArr As Variant
Dim res As Variant

myArr = ActiveSheet.Range("a1:b20").Value 'just test data
'(but it's a 2D (20x2) array)

With Application
res = .Match(CLng(Date), .Index(myArr, 0, 1), 0)
If IsError(res) Then
MsgBox "Not Found"
Else
MsgBox Application.Index(myArr, res, 2)
End If
End With

End Sub

My dates in A1:a20 were formatted as mm/dd/yyyy and I couldn't get this to
find
the match:
res = .Match(Date, .Index(myArr, 0, 1), 0)
And neither did this:
res = .Match(CLng(Date), .Index(myArr, 0, 1), 0)

But this did work (but it's too restrictive to use (well, I think)):
res = .Match(Format(Date, "mm/dd/yyyy"), .Index(myArr, 0, 1), 0)





"ExcelMonkey <" wrote:

I have a 2d VBA array. The first column has dates. The second column
has data. I want to do a lookup in column 1 off data column using a
date variable and extract the associated data element in column 2.

How do you do that in VBA?

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

--

Dave Peterson


--

Dave Peterson