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 FormulaArray command. However I am getting: 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 FormulaArray input 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 .Replace command to overcome the word limit.
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.
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.
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 EDIT Following 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*cell and so does not require FormulaArray. When I ran the code, this commands are executed well.
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 FormulaArray line. N.B. I checked the number of characters in formula and 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).
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
I changed the #NULL! that you had to 1E+99 so 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 after the 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.
' 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 .Replace functionality would have defaulted to what was last used. If this was xlWhole then the .Replace and the subsequent .FormulaArray assignment would again fail. I've modified to specify the , lookat:=xlPart parameter.