Results 1 to 6 of 6

How to activate multiple array formulas at once

This is a discussion on How to activate multiple array formulas at once within the Excel Questions forums, part of the Question Forums category; Hello everyone, I am working with an excel sheet that has hundreds different array formulas. I used "Find and Replace" ...

  1. #1
    New Member
    Join Date
    Dec 2013
    Posts
    31

    Default How to activate multiple array formulas at once

    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!

  2. #2
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    696

    Default Re: How to activate multiple array formulas at once

    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?

  3. #3
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    696

    Default Re: How to activate multiple array formulas at once

    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

  4. #4
    New Member
    Join Date
    Dec 2013
    Posts
    31

    Default Re: How to activate multiple array formulas at once

    Quote Originally Posted by StephenCrump View Post
    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

  5. #5
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    696

    Default Re: How to activate multiple array formulas at once

    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?

  6. #6
    New Member
    Join Date
    Dec 2013
    Posts
    31

    Default Re: How to activate multiple array formulas at once

    Quote Originally Posted by StephenCrump View Post
    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: Count(if(if(..))) not working)

    Have a great weekend!
    Last edited by bluesky6688; May 31st, 2014 at 03:53 PM.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com