# How to convert Matrix (Index/Match) into VBA Code?

1. ## How to convert Matrix (Index/Match) into VBA Code?

Hi guys,

I have some formulas to work with my worksheet, but because of the amount of data, it takes some time to do the tasks and i'm trying to do in VBA to make it faster.

Now i have this array formula below:

{=INDEX(Dump!J:J,MATCH(I7&"0",Dump!A:A&Dump!J:J,0))}

I want to know if the value of I7 cell on column A, have a "0" on column J.

How can i translate this formula to VBA code ?

2. ## Re: How to convert Matrix (Index/Match) into VBA Code?

i'm trying to do in VBA to make it faster
Just for info before you pursue this - No VBA code is faster than worksheet formulas...

3. ## Re: How to convert Matrix (Index/Match) into VBA Code?

To make the formula faster instead of Range as A:A Type as A1:A20000, if your range of data is upto A20000.

4. ## Re: How to convert Matrix (Index/Match) into VBA Code?

Originally Posted by cytop
Just for info before you pursue this - No VBA code is faster than worksheet formulas...
In my case, and for the amount of data from my sheets, VBA is faster than the formulas, because i want to fill one sheet with the information of others, and i don't want the recalculate of formula when i'm working on the sheet.

5. ## Re: How to convert Matrix (Index/Match) into VBA Code?

Originally Posted by kvsrinivasamurthy
To make the formula faster instead of Range as A:A Type as A1:A20000, if your range of data is upto A20000.
thank's but some sheets have 80000 rows and maybe more in some cases.

There are currently 1 users browsing this thread. (0 members and 1 guests)