Unable to set the FormulaArray property of the Range class

Hi

I am trying to change an array formula via code, using the following code, basically adding *(Assumed FTE)* after the Contractor=Yes criteria.

Please Login or Register  to view this content.

This gives a formula like

=SUM((tblEmployees[Type]='Resource Planner Data'!$A7)*(tblEmployees[Contractor]="Yes")*(tblEmployees[Assumed FTE])*(tblEmployees[Start]<'Resource Planner Data'!F$2)*((tblEmployees[End]>'Resource Planner Data'!F$3)+(tblEmployees[End]="")))*$C$4

But the error of

Unable to set the FormulaArray property of the Range class on this line

c.FormulaArray = strNewFormula

Many thanks

Nathan,

if I cut and paste the string into the cell CSE it, it works fine.

You cannot change a multi-cell array formula one cell at a time. You would Clear\Change all the cells in the multi-cell array all at once.

Try something like this (not tested). Assumes you have only one Multi-Cell "Contractor" array.

Please Login or Register  to view this content.

Perhaps a Named Range would be better than looping through the cells to determine the multi-cell array range.