skulakowski

07-19-2011, 02:56 PM

I have three named ranges in my spreadsheet, Quarters, UpdateMonth and Data.

My goal is to set up an array X with sumproduct calculation for specific Quarter and UpdateMonth combinations in each array element.

=SumProduct( if (Quarters="2005Q3",1,0), if (UpdateMonth=201001,1,0), Data)

I've got the loops set up to construct the various Quarters and UpdateMonth combinations as a string variable named ArrayCalc.

But I cannot see how to dim the array X, enter each ArrayCalc string as a .FormulaArray equation, or calculate the result of the sumproduct equation.

Is .FormulaArray limited to Range? I can imagine looping through my data and evaluating/summing on a row-by-row basis but that seems tedious when sumproduct exists. Anybody know of a clever way to make sumproduct work for me?

dim i, n as integer

dim X(0 to 10, 0 to 15) as variant

X(i,n).formulaarray = ArrayCalc

'Returns an "object required" error. The required object seems to be a range.

My goal is to set up an array X with sumproduct calculation for specific Quarter and UpdateMonth combinations in each array element.

=SumProduct( if (Quarters="2005Q3",1,0), if (UpdateMonth=201001,1,0), Data)

I've got the loops set up to construct the various Quarters and UpdateMonth combinations as a string variable named ArrayCalc.

But I cannot see how to dim the array X, enter each ArrayCalc string as a .FormulaArray equation, or calculate the result of the sumproduct equation.

Is .FormulaArray limited to Range? I can imagine looping through my data and evaluating/summing on a row-by-row basis but that seems tedious when sumproduct exists. Anybody know of a clever way to make sumproduct work for me?

dim i, n as integer

dim X(0 to 10, 0 to 15) as variant

X(i,n).formulaarray = ArrayCalc

'Returns an "object required" error. The required object seems to be a range.