How to activate multiple array formulas at once

bluesky6688

New Member
Joined
Dec 24, 2013
Messages
34
Hello everyone,

I am working with an excel sheet that has hundreds different array formulas. I used "Find and Replace" to modify the formulas to create more new formulas, then I found I need to do the CSE for each cell.

I tried the code I found from Excel Forum but I got a error says "Run-time error '1004': Unable to set the FormulaArray properties of the Range class". The problem was from the line "UsrCell.FormulaArray = UsrCell.Formula".

Here is the link to that post:

I have posted a sample work at the end of the post.

Any ideas?
Thanks!
 

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,506
So the problem is that this piece of code is not working?

Code:
For Each usrcell In Selection
    usrcell.FormulaArray = usrcell.Formula
Next usrcell
For the workbook you've posted on ExcelForum, which particular range are you selecting that gives you the error?
 

bluesky6688

New Member
Joined
Dec 24, 2013
Messages
34
Never mind, I think I found the problem.

An array formula must be 255 characters or less, and some of your formulae are longer. You can work around the problem by breaking the formula into chunks. Take a look here, for example:

Daily Dose of Excel » Blog Archive » Entering Long Array Formulas in VBA
Hi StephenCrump, thanks for your help.

You are right, the VBA code works if I reduce the formula length.

However, all of my formulas are single-cell array formulas, usually used to calculate averages and standard errors on multiple criteria. If I understand correctly the Replace function can only be used for same formula in multiple cells. Is that right? Is there a solution for my problem?

Thanks again
 

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,506
You can use Replace on a single cell.

But the problem with the method for getting around the maximum 255 string length is that the intermediate steps need to parse, i.e. you can't just chop the formula into random strings. So if every formula is different, it's not going to be easy to automate.

Seriously, I think your best short term solution is to make the changes manually. You said you had hundreds of formulae, but it looks to me like only a couple of dozen?

Longer term, I suggest that you could streamline a lot of these formulae, e.g. use COUNTIFS rather than COUNT(IF(IF(IF(IF(..)))).

Also, couldn't you use a PivotTable to generate just about all these results?
 

bluesky6688

New Member
Joined
Dec 24, 2013
Messages
34
You can use Replace on a single cell.

But the problem with the method for getting around the maximum 255 string length is that the intermediate steps need to parse, i.e. you can't just chop the formula into random strings. So if every formula is different, it's not going to be easy to automate.

Seriously, I think your best short term solution is to make the changes manually. You said you had hundreds of formulae, but it looks to me like only a couple of dozen?

Longer term, I suggest that you could streamline a lot of these formulae, e.g. use COUNTIFS rather than COUNT(IF(IF(IF(IF(..)))).

Also, couldn't you use a PivotTable to generate just about all these results?
Stephen, these are only a small part of my data. I have tried PivotTable earlier. I liked this way better because I show different ways of visualizing my data in one sheet, but maybe I just haven't learned PivotTable very well.

Talking about the COUNT(IF(IF(...))) function, it worked perfectly in my previous Excel sheets, but it suddenly wouldn't work today when I tried to make new similar formulae. I am going to start a new thread. (Here is the link: http://www.mrexcel.com/forum/excel-questions/781357-count-if-if-not-working.html#post3824853)

Have a great weekend!
 
Last edited:

Forum statistics

Threads
1,077,778
Messages
5,336,248
Members
399,072
Latest member
abublitz

Some videos you may like

This Week's Hot Topics

Top