Using Evaluate With Array Formula

If I use the below array formula in cell then it's working fine

Please Login or Register  to view this content.

But it's not working when I try to use in VBA code with Evaluate function which returns #VALUE Error

Please Login or Register  to view this content.

I am unable to fix what is going wrong

I suspect the problem is [@Advisor] since the evaluate method has no context in which to evaluate that

@ JosephP,

Thanks for the reply

Advisor is the one of the column header of the current Table and @ is used for current row reference of the table. But it's working fine when I enter it in cell.

I am attaching the sample file for your reference. Please have a look in B11 cell of Controls & Checks sheet.

in a cell the formula has a context for "this row". the evaluate method has no such context (as it's a method of the application or worksheet not a range). if you replace [@Advisor] with $A11 does it calculate correctly?

Yes It's working fine if I do like that

Thanks for making me to come out of this puzzle. I was trying to fix it for more than 3 hours.

Unfortunately now I am unable to add reputation to you since I have to spread it to someone to rep you again. I will add rep after spreading it for someone

don't sweat it-I just like to help :-)

............