Named range lookup using Index/Indirect/Match Function calls in Macro

Hi
I have a cell which has the following formula.
(INDEX(INDIRECT(W16),MATCH(B$3&B$4&"C",INDIRECT(X16),0),10)
It uses an Indirect function to know which sheet to lookup. I use named list (range) to define the index lookup array. The named range variable in the above formula is in cell W16. X16 is yet another named range variable. Match function returns the first element of the index array variable and the second array variable is a constant. In this scenario 10.

Now I want this whole thing to be called in a macro function. I would like to update the cell value with the above formula (instead of entering it directly in the cell)
e.g. Cells(2,2).value = (INDEX(INDIRECT(W16),MATCH(B$3&B$4&"C",INDIRECT(X16),0),10)

This is somewhat similar to this question http://www.excelforum.com/excel-form...and-match.html
but I would like to be able to call this in a macro function.

Any help would be appreciated.

Thanks a lot

I would try

Please Login or Register  to view this content.

note the double quotes within the formula. You could also just use an apostrophe instead of double quotes if you wanted.