Run-Time error 1004 - VBA copying down formula

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
716
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,996
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
716
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
716
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,334
Messages
5,624,071
Members
416,010
Latest member
NJT

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