当前位置: 动力学知识库 > 问答 > 编程问答 >

excel - 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.

分享给朋友:
您可能感兴趣的文章:
随机阅读: