Macro to speed up index/match

[jett254]

Hi,

I have an Excel worksheet that has to do an index/match to look up data from another sheet. It has 14 rows and ~8,000 columns -- each with an index/match. So, as you can imagine, the file is huge (60 MB) and too slow to be usable. I'm thinking I could instead have a macro do the lookups?

I'm just not sure how to get started? If I had a table to lookup in another worksheet, how would I do something faster??

 

[offthelip]

One of the main reasons that Vba is slow is the time taken to access the worksheet from VBa is a relatively long time.
To speed up vba the easiest way is to minimise the number of accesses to the worksheet. What is interesting is that the time taken to access a single cell on the worksheet in vba is almost identical as the time taken to access a large range if it is done in one action. So to solve your problem first load all of your data into a variant array:

inarr=range(cells(1,1),cells(14,8000))

This is 14 rows by 8000 columns
then use a double loop to go through this array using two simple index and a simple comparison:

for i = 1 to 14
 for j = 1 to 8000
   if inarr(i,j)=Searchstring then
      ' you have found a match and the index is  i row down and j columns across
     'do whatever you want to do with the match index here
 end if
next j
next i

Looping through 112000 cells like this should take less than a second. Doing it using ranges would take minutes.