+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    05-03-2017
    Location
    Lisbon, Portugal
    MS-Off Ver
    2016
    Posts
    3

    Question 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 ?


    Very thanks in advance
    Last edited by PRMiranda; 05-03-2017 at 09:19 AM. Reason: missing words

  2. #2
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    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. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    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. #4
    Registered User
    Join Date
    05-03-2017
    Location
    Lisbon, Portugal
    MS-Off Ver
    2016
    Posts
    3

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

    Quote Originally Posted by cytop View Post
    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. #5
    Registered User
    Join Date
    05-03-2017
    Location
    Lisbon, Portugal
    MS-Off Ver
    2016
    Posts
    3

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

    Quote Originally Posted by kvsrinivasamurthy View Post
    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] VBA to convert a coloured matrix into an equivalent matrix of 1's and 0's
    By The_Snook in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-27-2017, 01:12 PM
  2. Convert vlookup to INDEX and Match
    By welshman010 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-03-2014, 01:41 PM
  3. [SOLVED] Matrix Building using VLOOKUP, INDEX and MATCH
    By Saarang84 in forum Excel Formulas & Functions
    Replies: 27
    Last Post: 05-28-2014, 01:24 AM
  4. Replies: 11
    Last Post: 03-26-2014, 12:48 AM
  5. Replies: 2
    Last Post: 02-12-2014, 01:12 PM
  6. Help Match Index Formula for a table matrix
    By prkhan56 in forum Excel General
    Replies: 2
    Last Post: 09-29-2011, 04:40 AM
  7. How to convert Index(Match into VBA
    By Alternsti in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-14-2010, 07:39 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1