Copy and Paste Without a Macro
To work around this problem when you copy and paste the formula manually, follow these steps:
- Select the cell that contains the array formula (for example, cell A1).
- Press F2, and then press ENTER.
If you receive the error message "You cannot change part of an array"
this array formula is entered into multiple cells. Click OK.
- Select the range of cells, including the original cell, into which
the array formula should be entered (for example A1:A10). Make sure
that the cell that contains the formula is the active cell.
- Press F2, then press CTRL+SHIFT+ENTER.
The array formula is now entered into the entire range of cells.
Copy and Paste with a Macro
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
To work around this problem when you copy and paste the formula
with a macro, perform the following steps in your macro:
- Read the array formula from the original cell, and store the formula in a variable.
- Clear the original cell.
- Apply the stored formula to all of the cells in the range, including
the original cell.
The following sample macro demonstrates how to do this. In this example,
cell A1 contains the array formula "=SUM(IF(B1:B100=0,1,0))", (without
quotation marks). The macro applies this formula to the range A1:A10.
Sub CopyArrayFormula()
'stores formula in variable
xFormula = Range("A1").Formula
'clears formula from cell
Range("A1").Clear
'applies formula
Range("A1:A10").FormulaArray = xFormula
End Sub