Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Array formula - applying { } to a range of data

Posted on 2012-09-19
Medium Priority
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
  • 3
  • 2
LVL 50

Accepted Solution

Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 38413154


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

ID: 38413155
Thanks for the feedback Teylyn

How would u use vBa to complete the task - please..
LVL 50
ID: 38413165
route, I edited my first comment before you posted your follow-up. If you still have questions, please pipe up.

cheers, teylyn

Author Comment

ID: 38413167
LVL 50
ID: 38413211
No need to apologize. Glad that my suggestion helped. Thanks for the grade!

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

564 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