Find a value in an array variable (without looping)

[GBagley]

What would be the fastest way to find a value in a (large, ~25,000 member) array variable and return it's position? Lets assume a 1 dimensional array. I would that using loops or worksheetfunctions would be slower than using an inbuilt VBA method like .find -- what's the best way to do this?

 

[VoG]

Match is pretty fast

Sub test()
Dim i, x
x = Array("a", "b", "c", "d", "e", "f")
i = WorksheetFunction.Match("c", x, 0)
MsgBox x(i - 1)
End Sub

 

[Tom Urtis]

deleted

 

[GBagley]

I tried Match, but it was slower than I was hoping. I was thinking that storing data in an array variable and then searching it from within the code (using Match) would be faster than having the code reference back and forth searching between the different worksheets (using .find or vlookup), but it was quite a bit slower.

I ended up getting a significant speed increase by using a couple large, autofilled down, array formulas (which calculate much faster than I would have expected!) to initially consolidate all the raw data that the code needed to reference into a single worksheet. Increased the speed of my macro about 10x