Copy and paste array formulas using VBA

Question

I have some formulas in a row. I want to copy them down to the end of the rest of my data.

When using normal formulas, the following code works:

With Sheets("Sheet1")
    Set formRange = Range(.Range("G2"), .Range("O2").End(xlToRight))
    formRange.Copy
    formRange.Resize(Range("D" & Rows.Count).End(xlUp).Row - 1).PasteSpecial Paste:=xlPasteFormulas
    Application.CutCopyMode = False
    .Range("A1").Select
End With

However, when I replace some formulas with array formulas, I get a Run-time error 1004 that says PasteSpecial method of Range class failed.

Is there any way around this?

What your code does is copy and paste in the same Range. So if the result of formRange.Resize(...) is greater than 1 row (since you are resizing just that and not offsetting or anything) it will throw subscript due to Excels Rule that you cannot change part of the array. — L42
– L42, Commented Aug 5, 2014 at 22:44

L42 · Accepted Answer · 2014-08-05 23:01:42Z

As commented, you cannot change part of an array. So try this:

Dim formRange As Range, arrForm As String

With Sheets("Sheet1")
    Set formRange = Range(.Range("G2"), _
        .Range("O2").End(xlToRight))
    arrForm = formRange.FormulaArray
    formRange.ClearContents
    formRange.Resize(.Range("D" & _
        .Rows.Count).End(xlUp).Row - 1).FormulaArray = arrForm
End With

Btw, take note of the extra dots I put in this line:

formRange.Resize(.Range("D" & _
    .Rows.Count).End(xlUp).Row - 1).FormulaArray = arrForm

I assumed that you are pertaining to D Column of the same sheet.
Above works if it is just one array formula output in several ranges.
If each cell has different array formula, then just add offset in your code like this:

With Sheets("Sheet1")
    Set formRange = Range(.Range("G2"), _
        .Range("O2").End(xlToRight))
    formRange.Copy
    formRange.Offset(1, 0).Resize(.Range("D" & _
        .Rows.Count).End(xlUp).Row - 1).PasteSpecial xlPasteFormulas
End With

Martin Jackson · Accepted Answer · 2014-08-05 19:24:25Z

1

You need to use the Range.FormulaArray method:

With Worksheets("Sheet1")
    Set formRange = Range(.Range("G2"), .Range("O2").End(xlToRight))
    formRange.Copy
    Set newRange = (Range("D" & Rows.Count).End(xlUp).Row - 1)
    newRange.FormulaArray = formRange
    Application.CutCopyMode = False
    .Range("A1").Select
End With

answered Aug 5, 2014 at 19:24

Martin Jackson

4502 silver badges9 bronze badges

5 Comments

Jeffrey Kramer Over a year ago

I follow the logic but I can't quite get it to work. The newRange gives me object required errors.

Martin Jackson Over a year ago

You need to define newRange, please put Dim newRange As Range at the top of your code.

Jeffrey Kramer Over a year ago

Hmm, I defined it but it's still erroring. I'll try to figure it out after lunch!

Jeffrey Kramer Over a year ago

Yeah, I got the syntax to work, but it's just pasting values. Not sure what's up

Martin Jackson Over a year ago

Can you try putting an equals sign in front of formRarange: newRange.FormulaArray = "=" & formRange. What did you change in the syntax to make it correct?

Collectives™ on Stack Overflow

Copy and paste array formulas using VBA

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related