Array formula - applying { } to a range of data

Posted on 2012-09-19
Last Modified: 2012-09-19
Hi Experts

Is there a quick way to apply { formula } to a range of data as opposed
To going intO each cell and hitting CRTL + SHIFT + RETURN

Note - each cell in the range has a different formula in it.
Question by:route217
    LVL 50

    Accepted Solution



    If you want to enter an array formula, you need to edit each cell and Ctrl-Shift-Enter the formula. Or, use VBA to cycle through the cells and array enter them.

    There is another type of multiple cell array, where you select a range of cells, enter a formula into the cell and confirm with Ctrl-Shift-Enter, but without knowing what formula you are using, it's hard to tell if that is a workaround for your situation.

    Here is the VBA code to turn the selected cells into array-entered cells:

    Sub arrayEnter()
    Dim cel As Range
    For Each cel In Selection
        cel.FormulaArray = cel.Formula
    Next cel
    End Sub

    Open in new window

    Paste the code into the VBE (righ-click the sheet tab, select "View Code", paste the code into the code window). Ctrl-click to select the desired cells, change to the VBE, click inside the code and hit F5.

    cheers, teylyn

    Author Comment

    Thanks for the feedback Teylyn

    How would u use vBa to complete the task - please..
    LVL 50

    Expert Comment

    route, I edited my first comment before you posted your follow-up. If you still have questions, please pipe up.

    cheers, teylyn

    Author Comment

    LVL 50

    Expert Comment

    No need to apologize. Glad that my suggestion helped. Thanks for the grade!

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now