Unable to set the FormulaArray property of Excel table column

[Shundra9]

I have an Excel table named "WT_DARK_STTC", with columns titled "STTC", "Tissue", and "Unit Distance", in addition to others. On another sheet, there is another table named "STTC", and I am trying to set the column formula of its fifth column. The formula I wanted is essentially a MEDIANIFS(), but since Excel provides no such formula, I had to use the array formula recommended in this SO post: Excel Median for multiple conditions - Stack Overflow. So here's my question, how do I set the column formula of an Excel table column to be an array formula? I thought the code below would suffice, but I get the error:

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

valFormula = "=MEDIAN(IF(WT_DARK_STTC[Tissue]=""1"",IF(WT_DARK_STTC[Unit Distance]=[@[Unit Distance]],WT_DARK_STTC[STTC])))"
ActiveSheet.ListObjects("STTC").ListColumns(5).DataBodyRange.FormulaArray=valFormula

Clearly the formula is less than 255 characters. It isn't really in A1 or R1C1 style, but using Application.ConvertFormula() also gives me errors so I've been trying to use the valFormula as is. I've tried Dim-ing valFormula as a String and as a Variant. I've tried using the column's .Range and .DataBodyRange properties. I've even tried storing the column's DataBodyRange in a temporary Range variable. What's even more aggravating, if I try to record a macro of me typing this formula into the first column cell and then hitting Ctrl+Shift+Enter for an array formula, the generated code is almost identical to that shown above (it just uses Selection.FormulaArray instead). Does anyone know how I can fix this very annoying issue?

 

[Domenic]

Try..

valFormula = "=MEDIAN(IF(WT_DARK_STTC[Tissue]=""1"",IF(WT_DARK_STTC[Unit Distance]=[@[Unit Distance]],WT_DARK_STTC[STTC])))"

[COLOR=darkblue]With[/COLOR] ActiveSheet.ListObjects("STTC").ListColumns(5).DataBodyRange
    .Formula = valFormula
    .FormulaArray = .Formula
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]

By the way, if the numbers in column "Tissue" are true numerical values and not numbers formatted as text, you'll need to remove the double quotes on either side of your criteria. So you'll need to replace...

=""1""

with

=1

Hope this helps!

 

[Shundra9]

@Domenic Awesome, that totally worked! Does this mean that you cannot set a Range's FormulaArray property without first setting its Formula property? And must they equal the same string?

By the way, if the numbers in column "Tissue" are true numerical values and not numbers formatted as text, you'll need to remove the double quotes on either side of your criteria.

Yes, I already made sure the Tissue column is formatted as text. Users could specify any string ID like "awesome tissue," but I've been testing with IDs like 1,2,3, etc. Good catch though

 

[Domenic]

Actually, in a Table, you can set the FormulaArray property for the first cell in the range, and the formula will automatically be copied down the column...

ActiveSheet.ListObjects("STTC").ListColumns(5).DataBodyRange[COLOR=#ff0000].Cells(1)[/COLOR].FormulaArray = valFormula