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.
Bookmarks