Array Lookup

  • Thread starter Thread starter ExcelMonkey
  • Start date Start date
E

ExcelMonkey

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




How do you do that in VBA
 
ExcelMonkey,

Sub test()
Dim arr() As Variant, i As Long, dtmTemp As Date, blnFound As Boolean

ReDim arr(100 - 1, 2 - 1)

'refill array with random numbers
dtmTemp = CDate("1-Jan-2003")
For i = 0 To 100 - 1
arr(i, 0) = dtmTemp + i
arr(i, 1) = Int(Rnd() * 1000)
Next

'search for a particular date
dtmTemp = CDate("10-Mar-2003")
blnFound = False
For i = 0 To 100 - 1
If arr(i, 0) = dtmTemp Then
blnFound = True
Exit For
End If
Next
If blnFound Then MsgBox arr(i, 1) Else MsgBox "Date not found"
End Sub
 
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)
 
I've never had trouble with this:

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


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



Tom said:
I've never had trouble with this:

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

I am surprised.
Click to expand...
 
Dave,

You may be running into the problem that the Value property converts to a
Date type when the range data is formatted as a date.
It would be interesting to know if the problem goes away when you use the
value2 property (ignores date formatting).

myArr = ActiveSheet.Range("a1:b20").Value2 'just test data


regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
 
Thanks for the reminder. With that change, it worked fine.

(Someday, that .value2's going to sink in!!!)
 
Back
Top