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)), "")"

 

[Eraengineer]

Although I do believe I am under the 255 max characters for this error to be tripped 231 to be exact....

 

[Jonmo1]

Although I do believe I am under the 255 max characters for this error to be tripped 231 to be exact....

Then logically the problem is not because it's too long..

It's probably the quotes.
You have to double up quotes when entering a formula with VBA.
[Department]))+1, ""), 1)), "")"
would be
[Department]))+1, """"), 1)), """")"

 

[BobUmlas]

You haven't met the character limit. You're missing quotation marks. For both sets of "" in the formula, you need to double them up so the end of your formula looks like this:
+1, """"), 1)), """")"
Every quote used inside a quoted string must be doubled.

 

[Eraengineer]

I tried that and I even removed the quotes and replace them with numbers to remove that issue altogether and no success....

 

[Eraengineer]

This is very upsetting because this array slows down everything so I do not always want it there....

 

[Eraengineer]

VBA wasn't liking this Range("Table10[Work Center]").FormulaArray so I called out a cell and it worked. Weird that you cannot call out the entire table but I guess it updates all the rows anyway....

 

[Jonmo1]

I see the problem..

You can't put multicell array formulas in a Table.
this line is telling VBA to put the formula in ALL cells within the Work Center column of the table..
Range("Table10[Work Center]").FormulaArray =

You'll have to put it in the first cell of the column, then fill down.

 

[Eraengineer]

I am having issues with the fill down.... Excel will not let you paste the arrays in the table either... I need to fill down to the end of the table.

 

[Eraengineer]

By table I mean paste all in the work center column