使用 VBA 复制和粘贴数组公式
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25146105/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copy and paste array formulas using VBA
提问by Jeffrey Kramer
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.
但是，当我用数组公式替换某些公式时，我收到一个运行时错误 1004，显示PasteSpecial method of Range class failed.
Is there any way around this?
有没有办法解决？
回答by L42
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 Columnof 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:
我假设您与同一张纸的D 列有关。
如果它只是多个范围内的一个数组公式输出，则上述方法有效。
如果每个单元格都有不同的数组公式，那么只需在代码中添加偏移量，如下所示：
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
回答by Martin Hymanson
You need to use the Range.FormulaArray method:
您需要使用 Range.FormulaArray 方法：
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
