Run-time Error: Unable to set the FormulaArray property of the Range Class

Question

I get the run-time error 1004: unable to set the FormulaArray property of the Range class. I have been researching this problem but I can't find a solution to it. It is not longer than 255 character. I am trying to put the formula in a new table column too, so maybe that is part of the problem.

Could the problem be the "Rows(R8C:RC)" Portion I am trying to make this a relative reference so that it will reference to the table the formula is in?

When I "debug.print Trans_ID" I get the correct code but maybe the R8C:RC is not changing to the correct cell reference.

Dim Trans_ID As String

'AccountNumber is equal to 1003

Trans_ID = "=IFERROR(INDEX(Journal,SMALL(IF(L_" & AccountNumber & _
     "=Journal[Acct. '#],Journal[Trans ID],""""),ROWS(R8C:RC)),MATCH(Ledger_" & _
     AccountNumber & "[[#Headers],[Trans ID]],Journal[#Headers],0)),"""")"


Worksheets("Ledger").ListObjects(NewTableName.Name).ListColumns( _
                   "Trans ID").DataBodyRange.FormulaArray = Trans_ID

This will return a single number. It works when I enter enter the formula in not using VBA.

Have you tried replacing the R1C1 notation with A1 notation and see how that goes? — Mathieu Guindon
I have tried that. I changed it to "C$8:C8" but it still doesn't work. When I do that and debug.print it puts the R8C:RC back into it. — Chase
If you write an array formula to the entire column I don't think the row number will update, I think you have to fill down after writing to the first cell. — BigBen
I did this and it worked correctly. ` ThisWorkbook.Worksheets("Ledger").Cells(8, LedgerAccountCell + _ 9).Select Selection.FormulaArray = Trans_ID ` — Chase
Does this throw an error: Worksheets("Ledger").ListObjects(NewTableName.Name).ListColumns( _ "Trans ID").DataBodyRange.Cells(1).FormulaArray = Trans_ID? — BigBen

BigBen BigBen · Accepted Answer · 2019-09-26T16:47:47

Relative references won't update if you try to write an array formula to multiple cells. Able to reproduce your error, albeit with a much simpler array formula. Range.FillDown should work though if you write the formula into the first cell.

With Worksheets("Ledger").ListObjects(NewTableName.Name).ListColumns("Trans ID").DataBodyRange
    .Cells(1).FormulaArray = Trans_ID
    .FillDown
End With

If you already have the formulas set to autofill in the table, the .FillDown is redundant.

Run-time Error: Unable to set the FormulaArray property of the Range Class

1 Answers