Help me minimize this formula

[Eraengineer]

I get the character limit error(unable to set the formula array of the range class) when running applying this array in vba....I am a little stummped on how to shrink it down to make it usable in vba. Any help would be greatly appreciated.

Range("Table10[Work Center]").FormulaArray = _
"=IFERROR(INDEX(Table9[Work Center], SMALL(IF(($A$4=Table9[Department])*(COUNTIF($A$6:A6, Table9[Work Center])=0), ROW(Table9[Department])-MIN(ROW(Table9[Department]))+1, ""), 1)), "")"

 

[Jonmo1]

If it's a table, you only have to put it in the first cell, it will auto fill by itself down the rest of the table's column.
Same as if you typed the formula in the table by hand