On Sun, 23 Jan 2005 16:08:58 -0600, Myrna Larson
wrote:

I just did more fiddling and was surprised by these results

1st routine:

Dim Rng as Range
Dim p As Variant 'because D won't be found
Dim D As Long
Dim i As Long

Set Rng = Worksheets("Sheet1").Range("A2:A200")
D = #12/31/2004#
For i = 1 to 1000
p = Application.Match(D, Rng, 0)
Next i

2nd routine:

Dim v As Variant
Dim p As Variant 'because D won't be found
Dim D As Long
Dim i As Long

v = Worksheets("Sheet1").Range("A2:A200").Value2
D = #12/31/2004#
For i = 1 To 1000
p = Application.Match(D, Rng, 0)
Next i

The first code, using MATCH with a worksheet range, took 29 milliseconds.
Searching the same data in a variant array, took 64 milliseconds, slightly
more than twice as long.

I wonder if VBA is copying all of the data in the array back to Excel's
dataspace every time MATCH is called?




I've had some adult beverages this evening, and maybe that's why I don't see
where, in your second routine, Rng is defined ???


--ron