Fast Index/Match Alternative in VBA

Hey guys,

I am just beginning to learn VBA. I knew a lot about Excel functions before making the leap to VBA so much of my excel knowledge is rooted in the functions. I am trying to create a code that needs to perform an =index(match()) kind of function. Basically I have an array that I place in the spreadsheet and then need to place their corresponding info next to them.

The only way I have been able to think of so far is to use the application.index and application.match inside a for loop to get it done. However, I have to select the array in the formula in each loop and I am going through large amounts of data. It takes 5+ minutes to run just that part of my code. Does anyone know any faster/more efficient way to run this kind of code? I am open to any suggestions and can give a better explanation of how I am doing it now if needed.

Thanks in advance

Instead of worksheetfunction.match you could use find method instead of index simply use addressing . But the gain will will probably not satisfy you.

If we were about medium size datasets - I'd go into operations not directly on worksheet ranges, but on arrays of data
like:

Please Login or Register  to view this content.

stop code here and look in the locals window what is in myarr.

But as we are talking about realy large datasets, probably database approach would let you increase code speed from minutes to seconds.
Keywors for search DAO ADO ADODB . And dont worry it can all (also datasource) be in excel. May be http://support.microsoft.com/kb/257819 or is a good starting point or may be http://www.vbadventure.com/2012/11/u...el-vba-part-1/

Thanks for the reply I will look into those.

I am wondering if I can make it even easier if I tell you that I have both the indexing and the match arrays already in arrays in VBA. I actually place them in the spreadsheet in order to use application.match/application.index. I have to do that because those functions rely on a spreadhseet range. I couldn't find a way to use the array already in VBA as the range. It really is inconvenient as I have the array, I have to place it in the spreadsheet, then I have to reference the array once every loop, and then I have to place the new arrays back in the spreadsheet.

Any suggestions based on that? Again, I can try to explain better if that is necessary.

Thanks

Hello RS15,

Rather than try to explain the problem, the data, the layout, etc., why not post a copy of the workbook? If it contains any sensitive information, redact it before you post.

Here is a dumbed down version of my spreadsheet.

FB test.xls

I marked with a comment the loop that is slowing everything down. It should run a little faster than described above because I took out a bunch of stuff and it will have to go through this twice. However, I believe that it is the index/match section in the loop that is slowing everything down. If you need any explanation of what is going on please let me know. If anyone can help me make this run faster/more efficient that would be amazing! And I would be ever grateful!

Thanks

I forgot to mention it's module 1