PDA

View Full Version : VBA Arrays - Faster alternative than Vlookup?



danmcdon
08-21-2012, 05:02 PM
Hi! I have several multi-dimensional arrays from which I am using different lookup values to pull data. I'm currently using WorksheetFunction.Vlookup which I think is going very slow. I have many iterations and several different arrays so the total impact in run time is pretty significant. What is the best way to lookup values in an array? Or any other strategies to speed things up? THANKS!!

------------------

'example

Dim ArrData

ArrData = range("data")

Value = Application.WorksheetFunction.Vlookup(LookupValue, ArrData, 5, 0)

Tinbendr
08-21-2012, 07:30 PM
I don't really have a answer for you, just opinions. :)

1. Unless you have other needs from your array's, I would skip them completely and use Range.Find.

2. Using brute force may be faster than Vlookup.

3. You can use binary search. (http://stackoverflow.com/questions/1857404/excel-find-speed-vs-vba-binary-search)

danmcdon
08-21-2012, 07:41 PM
Thanks for your opinions:)

How would Range.Find work with a multidimensional array? What does it return? I tried:

pos = Range("data").Find(LookupValue, LookIn:=xlValues, LookAt=xlWhole)

The result was a type mismatch error

p45cal
08-21-2012, 11:42 PM
How would Range.Find work with a multidimensional array? What does it return? I tried:

pos = Range("data").Find(LookupValue, LookIn:=xlValues, LookAt=xlWhole)

The result was a type mismatch error If you Dimmed pos, Dim it as Range and use:
set pos = Range("data").Find(LookupValue, LookIn:=xlValues, LookAt:=xlWhole) (note the added missing colon too), or if you're only searching in column 1 of the range then
set pos = Range("data").columns(1).Find(LookupValue, LookIn:=xlValues, LookAt:=xlWhole) .Find returns a range object, so if not found pos will be nothing, so use the likes of:if not pos is nothing then myValue = pos.offset(,4).value or you can use the .row property of pos to return the absolute row on the sheet.

Bob Phillips
08-22-2012, 12:10 AM
Have you tried sorting the data before running VLookup?

snb
08-22-2012, 12:15 AM
this will do:

on error resume next
msgbox Range("data").Find(LookupValue, ,xlValues, 1).address


To look in the third column of an array and returning the corresponding value in column 5:


Sub snb_002()
sn = Cells(1).CurrentRegion
c01 = sn(Application.Match("cc7", Application.Index(sn, , 3),0), 5)
End Sub