FormulaArray Producing Runtime 1004 Error

Question

I am trying to run the following procedure that entails placing an array formula in a range ("CA2010") on a sheet ("Slate Data").

I've tested the the array formula many times and it produces the desired results.

The sub below gets

run-time '1004' error: Unable to set the FormulaArray property of the Range class.

Sub countuniqueBINs()

Dim placementoutlook As Workbook
Set placementoutlook = Excel.Workbooks("Placement Outlook")
Dim sdws As Worksheet
Set sdws = placementoutlook.Sheets("Slate Data")

sdws.Range("CA2010").NumberFormat = "general"
sdws.Range("CA2010").FormulaArray = "=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(E2:E2000,ROW(E2:E2000)-ROW(E2),0,1)),MATCH(""~""&E2:E2000,E2:E2000&"""",0)),ROW(E2:E2000)-ROW(E2)+1),1))"

End Sub

I tried breaking the array formula into two parts based on some guidance out there, but it still did not resolve the issue.

"CA2010" is not a valid range name, or is that the actual cell address? Your formula works fine for me in A1, but not in CA2010 for some reason (though I can enter it manually there...). — Tim Williams
Hmm...CA2010 is a cell address (column CA, Row 2010), someplace way out of the way of my sheet data...but, of course, I tried putting this in E2001 (column E, Row 2001) and now...naturally, after hours of screwing around with this. It works. Thanks very much for the sanity check on this. We'll see if this is actually repeatable. — Chris Dumas
Weird - if anyone here knows why this works in one cell but not another, please chime in... — Tim Williams
I believe it's because that formula is over 255 characters in R1C1 format. If you make the references absolute it should work. — Rory
@Rory - thanks I didn't know that (the R1C1 conversion) was a thing but totally explains the problem. — Tim Williams

Rory Rory · Accepted Answer · 2020-02-18T10:25:21

The reason for the error is that the R1C1 format version of your formula exceeds 255 characters (even though the A1 style version is much shorter than that) due to the relative references. If you use absolute references, the formula is short enough to be entered using FormulaArray; if you can't do that, you'll need to use the workaround of splitting the formula into sections so that you can use the Range.Replace method after entering a shorter version.

FormulaArray Producing Runtime 1004 Error

1 Answers