Runtime Error: 1004 Unable to set FormulaArray property of Range class

I'm trying to get VBA to write a formula in a certain range of cells with values of lines define variables: Arr(,)

. Because in EXCEL I would press Ctrl + Shift + Enter the formula, I am using the command FormulaArray

. However, I get: Run-time error: 1004 Unable to set the FormulaArray property of the Range Class

.

I have carefully checked the string format of the formula by VBA-printing as string in cell and comparing it to my normal EXCEL input. Therefore, the formula must be perfect. I checked the length of the input FormulaArray

and made sure it is well below 255 characters. Following a suggestion from the internet ( http://dailydoseofexcel.com/archives/2005/01/10/entering-long-array-formulas-in-vba/ ), I used a command .Replace

to overcome the word limit.

I also tried replacing the command With Sheets("Detail analysis").Cells(a, j)

with With Sheets("Detail analysis").Range(Cells(a,j).Address(0,0))

; however this still gives a FormulaArray error.

However, I still get the error: Run-time error: 1004 Unable to set the FormulaArray property of the Range Class

. IMAGE OF QUESTIONS . When this error is displayed, the debugger points to the string: .FormulaArray = formulaP1

.

Can someone tell me where I am going wrong in 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

      

        
        
        
      

    

PICTURE OF THE QUESTIONS After some further testing, I tried to VBA code some of the conditions in the formula. This split the formula into two parts: one instruction =cell*cell*cell

and therefore does not require FormulaArray

. When I ran the code, these commands run well.

The second operator is a summation, which looks at a range of cells to calculate a value. The code now fails when my conditions require a string FormulaArray

. Notabene I checked the number of characters in formula

and they added up to 250 (less than the 255 limit as 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