VBA_Cell to Return Value of INDEX and MATCH Array formula

[mchilapur]

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:

Please Login or Register  to view this content.

VBA code:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

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.

[Kaper]

if you want in cel a value - use value, if you want to have there formula - use formulaarray
but - in case of formulaarray you have to prepare string with real formula, so something like :

Please Login or Register  to view this content.

with .value use the same operations as you had in real formula so rather:

Please Login or Register  to view this content.

[mchilapur]

Hello Kaper,thanks for your response.

I used your below code to update value directly into by cell, but it throws 'Type Mismatch' Error.
Any suggestions..?

Please Login or Register  to view this content.

May be because its not an array formula..? How do i make it an array formula..?

I have used your below cel.FormulaArray and updated it will its own value as below. That covers my intent.
But i more curious to know cel.value to update the value directly.

Please Login or Register  to view this content.