I am trying to write VBA code for an Excel macro to fill in a dynamic range with an array formula. The formula works fine in excel, and I found another thread showing to split the formula into different parts because of some sort of character limit in .FormulaArray functions. But now I’m running into a type mismatch error when I’m trying to replace the dummy code with the rest of my actual formula.

(this is my first ever post here so I apologize for any bad formatting)

Below is my VBA code, it runs until the final line then gives a type mismatch error that I can’t seem to figure out the reason

formulaPart = "MATCH(1,(RebateKey[[Key]:[Key]]=R1C)*(RebateKey[[Start]:[Start]]<=RC1)*(RebateKey[[End]:[End]]>=RC1),0))," & Chr(10) & "   IFERROR(INDEX(RebateKey[[Rate]:[Rate]],MATCH(1,(RebateKey[[Key]:[Key]]=LEFT(R1C,SEARCH(""_"",R1C))&""ALL"")*(RebateKey[[Start]:[Start]]<=RC1)*(RebateKey[[End]:[End]]>=RC1),0))," & Chr(10) & "   INDEX(RebateKey[[Rate]:[Rate]],MATCH(1," & _
        "(RebateKey[[Key]:[Key]]=RIGHT(R1C,LEN(R1C)-SEARCH(""_"",R1C)+1))*(RebateKey[[Start]:[Start]]<=RC1)*(RebateKey[[End]:[End]]>=RC1),0))))" & _
        """"
                .Range(rates.Cells(2, 2), rates.Cells(ratesLR, ratesLC)).FormulaArray = _
                    "=IFERROR(INDEX(RebateKey[[Rate]:[Rate]],""X_X_X""),)"
                .Range(rates.Cells(2, 2), rates.Cells(ratesLR, ratesLC)).Replace """X_X_X""),)", formulaPart