FormulaArray does not work for more than 255 characters

**linok** · 12-18-2014, 05:00 AM

Hi Guys,

I am trying to FormulaArray in my code and it does not work for formula having more than 255 characters. I have already tried couple of solutionas given on link below and this does not work for me

http://www.excelforum.com/excel-prog...ml#post3932754
Solution on this link does not work for formula with more than 255 characters

http://dailydoseofexcel.com/archives...comment-694109
Solution on this link gives me error: 'application-defined or object-defined '

here is my formula:

Worksheets("WorkSheet1").Range("B1:B12").FormulaArray = "=IF(ISERROR(MEDIAN(IF(WorkSheet!R2C4:R10000C4=R50C2,IF(WorkSheet!R2C7:R10000C7=R51C2,IF(WorkSheet!R2C12:R10000C12=RC1,IF(WorkSheet!R2C18:R10000C18=R2C17,WorkSheet!R2C13:R10000C13)))))),0,MEDIAN(IF(WorkSheet!R2C4:R10000C4=R50C2,IF(WorkSheet!R2C7:R10000C7=R51C2,IF(WorkSheet!R2C12:R10000C12=RC1,IF(WorkSheet!R2C18:R10000C18=R2C17,WorkSheet!R2C13:R10000C13))))))"

Any help in this is highly appreciated.....

Thanks,
-N

**Andy Pope** · 12-18-2014, 05:31 AM

It says in the help.

The FormulaArray property also has a character limit of 255.

So be a bit creative and reduce the size of the formula. You can replace the range references with named ranges.

Worksheets("WorkSheet1").Range("B1:B12").FormulaArray = "=IF(ISERROR(MEDIAN(IF(DATA1,IF(DATA2=R51C2,IF(DATA3=RC1,IF(DATA4=R2C17,DATA5)))))),0,MEDIAN(IF(DATA1=R50C2,IF(DATA2=R51C2,IF(DATA3=RC1,IF(DATA4=R2C17,DATA5))))))"

You can even create the named ranges on the fly and then replace them in the formula for the full reference once the array formula is in place.

**linok** · 12-18-2014, 05:43 AM

Thanks Andy,

Yeah I knew FormulaArray has 255 character limitation and hence this post. I don't 100% agree to your creative approach as even after creating name ranges on the fly , the formula length might very well go beyond 255 characters.

I am looking for long term solution mate

**Andy Pope** · 12-18-2014, 05:46 AM

As I said, "You can even create the named ranges on the fly and then replace them in the formula for the full reference once the array formula is in place. "

So

Please Login or Register  to view this content.

Edit: Re reading your reply I see you are saying that even with reduced sections of the formula you may still exceed the limit. In that case you would need to use an alternative to a single cell formula solution.

**linok** · 12-18-2014, 11:14 PM

"In that case you would need to use an alternative to a single cell formula solution."

Did you mean looping for each cell value? I think this will increase the execution time.

please provide example ...

Thanks
-N

**Andy Pope** · 12-19-2014, 04:27 AM

Build a data set to perform the Median function on.

I'm not saying it's going to be quicker but given there is no way around the 255 limit, if you have extremely long references, you have no choice.