Run-Time error 1004 - VBA copying down formula

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
798
Office Version
  1. 365
Platform
  1. Windows
I am getting the following error:

1594841837774.png


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"

VBA Code:
  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"")"

1594842159633.png
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,053
Office Version
  1. 365
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

Well-known Member
Joined
Jul 24, 2019
Messages
798
Office Version
  1. 365
Platform
  1. Windows
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:

1594844223120.png


VBA Code:
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?

1594844056132.png
 

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
798
Office Version
  1. 365
Platform
  1. Windows
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 :)

VBA Code:
    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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,014
Messages
5,834,931
Members
430,326
Latest member
tomwax46

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top