Evaluating Array Formula in VBA and sending Results Only to Spreadsheet

I am trying to evaluate an array formula in VBA and send only the results to my spreadsheet...

This is the formula:

Please Login or Register  to view this content.

and I want it to go from Row 1 to 6000....

I tried using Evaluate

Please Login or Register  to view this content.

What's the best way to get this right?

Ok... I think I got it with this:

Please Login or Register  to view this content.

but is there a better way?

NBVC,

That formula could be changed to

=(-1)^AA1 * (2^AR1 + SUMPRODUCT(AJ1:AP1, 2^(AR1 - {1,2,3,4,5,6,7} ) ) + SUMPRODUCT(K1:Z1, 2^(AR1 - {8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23}) ) )

And the next one to

=SUMPRODUCT(AB1:AI1, {128,64,32,16,8,4,2,1}) - 127

Ok, thanks shg.

Is there a good way to VBA it other than how I did it above with the EVALUATE method?

You could insert the formulas en masse by column (eliminating the row loop), and then replace values with values. You'd have to try it to see what the performance gain is.

Please Login or Register  to view this content.

Thank you, shg