Results 1 to 3 of 3

VBA_Cell to Return Value of INDEX and MATCH Array formula

Threaded View

  1. #1
    Forum Contributor
    Join Date
    09-20-2014
    Location
    India
    MS-Off Ver
    2013
    Posts
    275

    Question VBA_Cell to Return Value of INDEX and MATCH Array formula

    Dear all,
    I am dealing with Array formulas for the first time in VBA and tried hard to figure out how best i can use it, but failed.

    I have 2 sheets 'Previous' and 'Current' and below is the INDEX and MATCH array formula to return a value from 'Previous' sheet.
    When i update this formula is worksheet, it successfully return a value.
    Can any one help how can return same value using this Array formula in VBA...?

    Formula:
    {=INDEX('Previous'!A:L,MATCH('Current'!J2&'Current'!L2,'Previous'!J:J&'Previous'!L:L,0),1)}
    VBA code:
    cel.FormulaArray = WorksheetFunction.Index(wsP.Range("A:L"), WorksheetFunction.Match(cel.Offset(0, 9).Value, wsP.Range("J:J"), 0) + WorksheetFunction.Match(cel.Offset(0, 11).Value, wsP.Range("L:L"), 0), 1)
    or
    cel.value= WorksheetFunction.Index(wsP.Range("A:L"), WorksheetFunction.Match(cel.Offset(0, 9).Value, wsP.Range("J:J"), 0) + WorksheetFunction.Match(cel.Offset(0, 11).Value, wsP.Range("L:L"), 0), 1)
    On way is, i can update the array formula directly into cell and re-paste only value into that cell.I know how to achieve this.
    But i want to know how to return only value in a cell by assigning a array formula in VBA.

    Thanks.
    Last edited by mchilapur; 03-18-2017 at 01:59 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 03-15-2017, 08:22 AM
  2. Replies: 3
    Last Post: 04-02-2016, 08:16 PM
  3. [SOLVED] Index/Match to Return 1D array to be used in a sumproduct
    By david0985 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-17-2014, 04:41 PM
  4. Replies: 4
    Last Post: 03-27-2014, 01:09 PM
  5. Use Index/Match Function to Return Values from an Array
    By 00pumpkin in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-11-2013, 02:22 PM
  6. Match/Index array in VBA to return cell address
    By djhsickboy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-02-2011, 02:18 AM
  7. Replies: 2
    Last Post: 02-27-2010, 11:17 AM

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