• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

Array formula - applying { } to a range of data

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.
0
route217
Asked:
route217
  • 3
  • 2
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

No.

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
0
 
route217Author Commented:
Thanks for the feedback Teylyn

How would u use vBa to complete the task - please..
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
route, I edited my first comment before you posted your follow-up. If you still have questions, please pipe up.

cheers, teylyn
0
 
route217Author Commented:
Apologies...
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
No need to apologize. Glad that my suggestion helped. Thanks for the grade!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now