Thread: "Unable to set FormulaArry property of the range class" - Error

The following formula has been working fine, through tests and mock up.

I have opened the file again today and tried running it to give the sheet a full test and it now comes up with the following error

Run Time Error "1004"

Unable to set FormulaArry property of the range class.

I have looked on the interweb and the only answer is that you cant have more than 255 charaters in an Array forumla, but how come its been working fine and then just stop working

Code as as below, I have put an arrow on the line its picking up

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Application.EnableEvents = False

    For Each cell In Target
        If Not Intersect(cell, Range("G2:G3000,H2:H3000,I2:J3000,M2:P3000")) Is Nothing Then
            If Application.WorksheetFunction.IsText(cell.Value) Then _
                cell.Value = UCase(cell.Value)
-->     ElseIf Not Intersect(cell, Range("D2:D10000")) Is Nothing Then _
            Cells(cell.Row, "E").FormulaArray = _
                "=MAX(IF(ISNUMBER(0+MID(RC[-1],1,ROW(R1:R4))),0+MID(RC4,1,ROW(R1:R4))))+IF(ISNUMBER(MATCH(RIGHT(RC[-1],1),R2C24:R27C24,0)),VLOOKUP(RIGHT(RC[-1],1),R2C24:R27C25,2,0)/10000,0)"
        End If
    Next cell
    
Application.EnableEvents = True
End Sub

As i said, this code has been working fine.

Hope someone can sheard some light on this

G

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.