Translate array formula to VBA code

[Villalobos]

Hello,

I would like to translate the below mentioned array formula to VBA code but I can not set the FormulaArray property. The expected results are in the column R.

The array formula:

=IF(COUNTIFS(BOM!C:C,D9,BOM!N:N,"ACTIVE MELANGE")/SUM(IF(D9=BOM!C9:C11991,1/(COUNTIFS(BOM!C9:C11991,D9,BOM!I9:I11991,BOM!I9:I11991)),0))=1,"Mono material",IF(COUNTIFS(BOM!C:C,D9,BOM!N:N,"ACTIVE MELANGE")/SUM(IF(D9=BOM!C9:C11991,1/(COUNTIFS(BOM!C9:C11991,D9,BOM!I9:I11991,BOM!I9:I11991)),0))=2,"Bi material","Not assigned"))

The code until now, but doesn't work:

Option Explicit
Sub GetData()
Dim LastRow As Long
Dim OutputSheet1 As Worksheet
Dim OutputLastRow1 As Long
Dim OutputSheet2 As Worksheet
Dim OutputLastRow2 As Long
Dim myFormula2 As String

    Set OutputSheet1 = Worksheets("BOM")
    Set OutputSheet2 = Worksheets("Evaluation")
     
    With OutputSheet2
        OutputLastRow2 = .Cells(.Rows.Count, "B").End(xlUp).Row
        myFormula2 = "=IF(COUNTIFS(BOM!R9C3:R" & OutputLastRow1 & "C3=RC[-4],D9,BOM!R9C19:R" & OutputLastRow1 & ",""ACTIVE MELANGE"")/SUM(IF(D9=BOM!R9C3:R" & OutputLastRow1 & ",1/(COUNTIFS(R9C3:R" & OutputLastRow1 & ",D9,BOM!R9C9:R" & OutputLastRow1 & ",BOM!R9C9:R" & OutputLastRow1 & ")),0))=1,""Mono material"",IF(COUNTIFS(BOM!R9C3:R" & OutputLastRow1 & "C3=RC[-4],D9,BOM!R9C19:R" & OutputLastRow1 & ",""ACTIVE MELANGE"")/SUM(IF(D9=BOM!R9C3:R" & OutputLastRow1 & ",1/(COUNTIFS(R9C3:R" & OutputLastRow1 & ",D9,BOM!R9C9:R" & OutputLastRow1 & ",BOM!R9C9:R" & OutputLastRow1 & ")),0))=2,""BI material"",""Not assigned""))"
        Range("Q9").FormulaArray = myFormula2
        Range("Q9").Copy Range("Q10:Q" & OutputLastRow2)
        Application.CutCopyMode = False
    End With
    
End Sub

The code is stop at this line:

Range("Q9").FormulaArray = myFormula2

Could somebody give me advice where I made mistake?

The sample file has been attached to this thread!



Thanks in advance the reply.