Unable to set the FormulaArray property of the Range Class

I am having some trouble with the following,
-I have noticed this error seems to come up when the formula is > 255 characters; people have then split the formula in two. The formula I have posted is 223 character and I currently have this formula in the cells and it is working.

-I have only pasted the first part of the code i.e. the range going from H5:H29, I basically have the same formula except the LARGE part changes by 1.

Please Login or Register  to view this content.

Try this instead:

Please Login or Register  to view this content.

No loop.

JBeaucaire,

Thank you for the reply, this did actually work however I have had to change the formula a bit. I am somewhat familiar with r1c1 style however not sure I get it for the formula you had posted.

I was wondering if you'd be able to assist with the code below (as your suggestion did work with my original formula -

Please Login or Register  to view this content.

thanks so much

ROWS($A$5:A5) becomes ROWS(R5C1:RC)

$B$1 becomes R1C2

$M$17 becomes R17C13

Well... one trick that comes to mind is the fact that named formulas are by default treated as arrays. So, you could:

1) select the first cell
2) create a named range using that formula, call it MySpecialFormula or whatever
3) Then the formula you enter is a normal formula.
=MyFormula

Please Login or Register  to view this content.