Macro to Insert Formula Array

[skd25]

I am using a WorkSheet change event to run a couple of functions. So far I am having Range issues when inserting a formula array. Is there anything I missing in the code below?

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim KeyCells As Range
    Set KeyCells = Range("E4:E1100")
    
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
Application.EnableEvents = False

Range("M4").Copy
    Range("M5:M1100").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Range("M5:M1100").Value = Range("M5:M1100").Value
    Application.CutCopyMode = False
    
    Range("O2:O10").FormulaArray = "=IF(Master_Sales_Order="""",0, IFERROR(INDEX(value_for_posting,MATCH(Master_Sales_Order,IF(ISNUMBER(SEARCH(""comp"",part_type)),customer_order,0),0)),""NO P/O""))"
    
    Application.EnableEvents = True

  End If
    
End Sub

 

[MARK858]

Try

Range("O2").FormulaArray = "=IF(Master_Sales_Order="""",0, IFERROR(INDEX(value_for_posting,MATCH(Master_Sales_Order,IF(ISNUMBER(SEARCH(""comp"",part_type)),customer_order,0),0)),""NO P/O""))"
Range("O2").AutoFill Destination:=Range("O2:O10")

 

[skd25]

@MARK858: Thanks for the reply

Received an error message " Unable to Set the FormulaArray property of the Range Class"

 

[MARK858]

Didn't look at your actual formula as I thought it was a problem with the incrementing of the formula.
From Excel Help

This problem occurs when you try to pass a formula that contains more than 255 characters, and you are using the FormulaArray property in Visual Basic for Applications

.

Edit: Which I don't understand as I just done a Len on the characters and got 161?????

 

[MARK858]

I am really confused now. I ran the 2 lines I posted and I didn't get a " Unable to Set the FormulaArray property of the Range Class" error all I got was a name error, which is because I haven't set the sheets up with Master_Sales_Order and value_for_posting e.t.c.
I don't think I can be of any more help. Hopefully someone will step in.

Excel Workbook
	O
1	*
2	#NAME?
3	#NAME?
4	#NAME?
5	#NAME?
6	#NAME?
7	#NAME?
8	#NAME?
9	#NAME?
10	#NAME?
Referrals