Array Lookup
 

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

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

Array Lookup
 

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



--
Rob van Gelder - http://www.vangelder.co.nz/excel


"ExcelMonkey " wrote in message
...
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/

Array Lookup
 

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

Array Lookup
 

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

Array Lookup
 

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

Array Lookup
 

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

"Dave Peterson" wrote in message
...
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

Array Lookup
 

Thanks for the reminder. With that change, it worked fine.

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



Charles Williams wrote:

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


--

Dave Peterson