Run-Time error 1004 - VBA copying down formula

[willow1985]

I am getting the following error:

On the following section of code and I am not sure why. Any help would be appreciated. I have used this code before with no issues, however it never had table references or was array formulas....

Note: The table name is "DD"

  Range("AB3").Select
LastRowColumnG = Cells(Rows.Count, 7).End(xlUp).Row
    Range("AB3:AB" & LastRowColumnG).Formula = "=IFERROR(INDEX(Codes!C[-25],MATCH([@[Ship To Location]],Codes!C[-27],FALSE)),""N/A"")"
 
       Range("AC3").Select
LastRowColumnG = Cells(Rows.Count, 7).End(xlUp).Row
    Range("AC3:AC" & LastRowColumnG).FormulaArray = "=IF(ISNUMBER(MATCH([@[Due Date]]&""|""&[@[Order Type]]&""|""&[@[Deere Part Number]]&""|""&[@[Order Qty]]&""|""&[@[PO Number]]&""|""&[@[PO Line Number]]&""|""&[@[Ship to Location Code]],MF[Ship Date]&""|""&MF[Status]&""|""&MF[Part No]&""|""&MF[QTY Ordered]&""|""&MF[PO No]&""|""&MF[[PO Line ]]&""|""&MF[Address ID],0)),"""",""Flag"")"
 
       Range("AD3").Select
LastRowColumnG = Cells(Rows.Count, 7).End(xlUp).Row
    Range("AD3:AD" & LastRowColumnG).Formula = "=YEAR([@[Due Date]])"
 
       Range("AE3").Select
LastRowColumnG = Cells(Rows.Count, 7).End(xlUp).Row
    Range("AE3:AE" & LastRowColumnG).FormulaArray = "=IF(ISNUMBER(MATCH([@[Order Type]]&""|""&[@[Deere Part Number]]&""|""&[@[Order Qty]]&""|""&[@[PO Number]]&""|""&[@[PO Line Number]]&""|""&[@[Ship to Location Code]],MF[Status]&""|""&MF[Part No]&""|""&MF[QTY Ordered]&""|""&MF[PO No]&""|""&MF[[PO Line ]]&""|""&MF[Address ID],0)),"""",""Flag"")"

 

[Oaktree]

In the highlighted line, change .Formula to .FormulaR1C1.

C[-25] and C[-27] are R1C1 formula references, so you can't use them in an A1 formula.

 

[willow1985]

I realized since it is a table I do not need to copy down the formula, the table will do that for me. However I am still getting an error on the Array:

Range("AB3").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(INDEX(Codes!C[-25],MATCH([@[Ship To Location]],Codes!C[-27],FALSE)),""N/A"")"
    Range("AC3").Select
    Selection.FormulaArray = _
        "=IF(ISNUMBER(MATCH([@[Due Date]]&""|""&[@[Order Type]]&""|""&[@[Deere Part Number]]&""|""&[@[Order Qty]]&""|""&[@[PO Number]]&""|""&[@[PO Line Number]]&""|""&[@[Ship to Location Code]],MF[Ship Date]&""|""&MF[Status]&""|""&MF[Part No]&""|""&MF[QTY Ordered]&""|""&MF[PO No]&""|""&MF[[PO Line ]]&""|""&MF[Address ID],0)),"""",""Flag"")"
    Range("AD3").Select
    ActiveCell.FormulaR1C1 = "=YEAR([@[Due Date]])"
    Range("AE3").Select
    Selection.FormulaArray = _
        "=IF(ISNUMBER(MATCH([@[Order Type]]&""|""&[@[Deere Part Number]]&""|""&[@[Order Qty]]&""|""&[@[PO Number]]&""|""&[@[PO Line Number]]&""|""&[@[Ship to Location Code]],MF[Status]&""|""&MF[Part No]&""|""&MF[QTY Ordered]&""|""&MF[PO No]&""|""&MF[[PO Line ]]&""|""&MF[Address ID],0)),"""",""Flag"")"

Any idea what I am missing here?

 

[willow1985]

After a quick google I found that my formula's were too long. So I left the formula in the first row of the table and just did a VBA code to copy the formula down.
A work around that seems to be working.

Thank you for your time.

Any recommendations however are always appreciated.

Thanks again

    Range("AB3").Select
    Selection.AutoFill Destination:=Range("DD[Ship to Location Code]")
    Range("DD[Ship to Location Code]").Select
   
    Range("AC3").Select
    Selection.AutoFill Destination:=Range("DD[Flag with Dates]")
    Range("DD[Flag with Dates]").Select
   
    Range("AD3").Select
    Selection.AutoFill Destination:=Range("DD[Year]")
    Range("DD[Year]").Select
   
    Range("AE3").Select
    Selection.AutoFill Destination:=Range("DD[Flag without Date]")
    Range("DD[Flag without Date]").Select
    Range("AE3").Select