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!
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top