vba 运行时错误：1004 无法设置范围类的 FormulaArray 属性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26733266/
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
Run-time error: 1004 Unable to set the FormulaArray property of the Range Class
提问by Galju
I am trying to have VBA write a formula in a certain ranges of cells with row values defined by the variable: Arr(,). Because in EXCEL I would Ctrl+Shift+Enter the formula, I am using the FormulaArraycommand. However I am getting: Run-time error: 1004 Unable to set the FormulaArray property of the Range Class.
我试图让 VBA 在特定范围的单元格中编写一个公式，其行值由变量定义：Arr(,). 因为在 EXCEL 中我会按 Ctrl+Shift+Enter 公式，所以我使用的是FormulaArray命令。但是我得到：Run-time error: 1004 Unable to set the FormulaArray property of the Range Class。
I have thoroughly checked the string format of the formula by VBA printing is as a string in a cell and comparing it to my normal input in EXCEL. So the formula should be fine. I have checked the length of the FormulaArrayinput and made sure it is well below the 255 character limit. Following a suggestion from online (http://dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/), I used the .Replacecommand to overcome the word limit.
我已经通过 VBA 打印彻底检查了公式的字符串格式作为单元格中的字符串并将其与我在 EXCEL 中的正常输入进行比较。所以公式应该没问题。我检查了FormulaArray输入的长度并确保它远低于 255 个字符的限制。根据在线（http://dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/）的建议，我使用该.Replace命令来克服字数限制。
I have also tried to replace the With Sheets("Detail analysis").Cells(a, j)command with With Sheets("Detail analysis").Range(Cells(a,j).Address(0,0)); however this still gives the FormulaArray error.
我也尝试With Sheets("Detail analysis").Cells(a, j)用With Sheets("Detail analysis").Range(Cells(a,j).Address(0,0));替换命令 但是这仍然会导致 FormulaArray 错误。
Nevertheless, I am still getting the error: Run-time error: 1004 Unable to set the FormulaArray property of the Range Class. QUESTION EDIT: When this error is displayed the debugger points towards the line: .FormulaArray = formulaP1.
尽管如此，我仍然收到错误：Run-time error: 1004 Unable to set the FormulaArray property of the Range Class. 问题编辑：当显示此错误时，调试器指向行：.FormulaArray = formulaP1。
Can anyone suggest where I am going wrong with the code?
任何人都可以建议我的代码哪里出错了？
' Define variables '
Dim top As Integer
Dim bottom As Integer
Dim a As Integer
Dim sumrows As Double ' Summation of the Main Loads in a list '
Dim totalsum As Double ' Generator Loads total '
Dim etotalsum As Double ' Emergency Generator Loads total '
Dim g As Integer
Dim formulaP1 As String
Dim formulaP2 As String
Dim formulaP3 As String
Dim formulaP4 As String
Dim nill As String
nill = Chr(34) & Chr(34)
j = 6
' Loop for the number of "Actual Load" columns required '
Do While Sheets("Detail analysis").Cells(9, j).Value = Sheets("Detail analysis").Cells(9, 6).Value
totalsum = 0
etotalsum = 0
' Nested Loop for the list ranges identified by the previous code block (i.e. between orange and blue rows) '
i = 1
Do While Arr(i, 1) <> green ' Green is a previously defined row number '
''''' Identify the Orange (Top) and Blue (bottom) rows of the current list '
top = Arr(i, 1)
bottom = Arr(i, 2)
''''' Write formula in the "Actual Load" column between the Arr() rows '
For a = (top + 1) To (bottom - 1)
formulaP1 = "=IF(OR($B" & a + 1 & "=" & nill & ",$A" & a & "=" & nill & "),IF(OR($A" & a & "<>" & nill & ",$B" & a & "<>" & "X_X_X()"
formulaP2 = nill & "),$C" & a & "*$D" & a & "*" & Sheets("Detail analysis").Cells(a, j - 1).Address(0, 0) & "," & nill & ")," & "Y_Y_Y()"
formulaP3 = "SUM(" & Sheets("Detail analysis").Cells(a + 1, j).Address(0, 0) & ":INDIRECT(ADDRESS(SMALL(IFERROR(IF($A" & a + 2 & ":$A$" & bottom & "<>" & nill & "Z_Z_Z()"
formulaP4 = ",ROW($A" & a + 2 & ":$A$" & bottom & ")-1),#NULL!),1),COLUMN(" & Sheets("Detail analysis").Cells(a, j).Address(0, 0) & "),1,1,))))"
With Sheets("Detail analysis").Cells(a, j)
.FormulaArray = formulaP1
.Replace "X_X_X()", formulaP2
.Replace "Y_Y_Y()", formulaP3
.Replace "Z_Z_Z()", formulaP4
End With
Next a
Next a
i = i + 1
Loop
j = j + 2
Loop
QUESTION EDITFollowing some further trials I have tried to VBA code some of the conditions in the formula. This divided the formula in two: one statement is =cell*cell*celland so does not require FormulaArray. When I ran the code, this commands are executed well.
问题编辑经过一些进一步的试验，我尝试对公式中的一些条件进行 VBA 编码。这将公式一分为二：一个语句是=cell*cell*cell，所以不需要FormulaArray. 当我运行代码时，这个命令执行得很好。
The second statement is the summation which considers a range of cells to calculate the value. The code is now failing specifically when my conditions call for the FormulaArrayline. N.B. I checked the number of characters in formulaand they add up to 250 (less than the 255 limit stated on the MSDN website http://msdn.microsoft.com/en-us/library/office/ff837104(v=office.15).aspx).
第二个语句是求和，它考虑了一系列单元格来计算值。当我的条件要求该FormulaArray行时，代码现在特别失败。注意我检查了字符数，formula它们加起来为 250（小于 MSDN 网站http://msdn.microsoft.com/en-us/library/office/ff837104(v=office.15)上规定的 255 个限制).aspx)。
ws= Sheets("Detail analysis")
With ws
formula = "=SUM(" & .Cells(a + 1, j).Address(0, 0) & ":INDIRECT(ADDRESS(SMALL(IFERROR(IF($A" & a + 2 & ":$A$" & bottom & "<>" & nill & _
",ROW($A" & a + 2 & ":$A$" & bottom & ")-1),1E+99),1),COLUMN(" & .Cells(a, j).Address(0, 0) & "),1,1,))))"
End With
For a = (top + 1) To (bottom - 1)
If ws.Cells(a + 1, 2) = "" Or ws.Cells(a, 1) = "" Then
If (ws.Cells(a, 1) <> "" Or ws.Cells(a, 2) <> "") And ws.Cells(a, j - 1) <> "" Then
ws.Cells(a, j).formula = "=$C" & a & "*$D" & a & "*" & ws.Cells(a, j - 1).Address(0, 0)
End If
Else
ws.Cells(a, j).FormulaArray = formula
End If
Next a
采纳答案by Galju
I changed the #NULL!that you had to 1E+99so it would never be SMALL. Not sure where #NULL!comes from but it isn't an accepted Excel error code. I also changed the method of assembling the array formula, choosing to assemble it as a string in the cell and only make it an array formula afterthe replacements were made and the formula was fully formed. With no data to test on and some vars made up (the values were missing in the sample), I came up with this.
我改变了#NULL!你必须改变的，1E+99所以它永远不会是SMALL。不确定#NULL!来自哪里，但它不是可接受的 Excel 错误代码。我也改变了数组公式的组合方式，选择在单元格中组合成一个字符串，只有在进行替换并完全形成公式后才使其成为数组公式。由于没有要测试的数据和一些变量（样本中缺少值），我想出了这个。
' Write formula in the "Actual Load" column between the Arr() rows '
For a = (top + 1) To (bottom - 1)
With Sheets("Detail analysis")
formulaP1 = "'=IF(OR($B" & a + 1 & "=" & nill & ",$A" & a & "=" & nill & "),IF(OR($A" & a & "<>" & nill & ",$B" & a & "<>" & "X_X_X()"
formulaP2 = nill & "),$C" & a & "*$D" & a & "*" & .Cells(a, j - 1).Address(0, 0) & "," & nill & ")," & "Y_Y_Y()"
formulaP3 = "SUM(" & .Cells(a + 1, j).Address(0, 0) & ":INDIRECT(ADDRESS(SMALL(IFERROR(IF($A" & a + 2 & ":$A$" & bottom & "<>" & nill & "Z_Z_Z()"
formulaP4 = ",ROW($A" & a + 2 & ":$A$" & bottom & ")-1),1E99),1),COLUMN(" & .Cells(a, j).Address(0, 0) & "),1,1,))))"
With .Cells(a, j)
.Value = formulaP1
.Replace What:="X_X_X()", Replacement:=formulaP2, lookat:=xlPart
.Replace What:="Y_Y_Y()", Replacement:=formulaP3, lookat:=xlPart
.Replace What:="Z_Z_Z()", Replacement:=formulaP4, lookat:=xlPart
.FormulaArray = .Value
End With
End With
Next a
Addendunm:the .Replacefunctionality would have defaulted to what was last used. If this was xlWholethen the .Replaceand the subsequent .FormulaArrayassignment would again fail. I've modified to specify the , lookat:=xlPartparameter.
Addendunm：该.Replace功能会拖欠到什么是最后一次使用。如果是这样，xlWhole那么.Replace后续.FormulaArray分配将再次失败。我已经修改为指定, lookat:=xlPart参数。
