CODEWITHSUNDEEP
excelvba
Michi
Michi

Reputation: 5471

Copy array formula with VBA

I have the following Excel table:

          A                 B                    C                    D                       E
1                     Purchase Price        Sales Price      Purchase Price > Sales Price
2     Product A            50                    40        ={COUNT(IF(ISNUMBER(B2:B10),IF(B2:B10>C2:C10-10,B2:B10)))}
3     Product B            60                    65
4     Product C            80                    60
5     Product D            10                    20
6     Product E            50                    30
7

Now I want use the following Macro to copy the formulas from cell B2:D6 into cell E2:G6:

Sub Array_Formula()
Sheets("Sheet1").Range("E2:G6").Formula = Sheets("Sheet1").Range("B2:D6").Formula
End Sub

This macro copies the formulas from cell B2:D6 into cell E2:G6. However, it does not copy the { } of the formula in cell D2 so it does not stay as an array.

How do I have to change my macro so it copies the formula including the array?

Thanks for any help.

Upvotes: 0

Views: 2663

Answers (1)

Michi
Michi

Reputation: 5471

As already described in the comments the solution is the following VBA:

Sub Copy_Array_Formula()
Sheet1.Range("B2:D6").Copy
Sheet1.Range("E2:G6").PasteSpecial xlFormulas
End Sub

Upvotes: 0

Related Questions