View Full Version : Sleeper: Is .FormulaArray limited to Range?

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

Bob Phillips
07-20-2011, 09:03 AM
Show us all of the code.

07-25-2011, 01:26 PM
Dim i, n As Integer
Dim X(0 To 10, 0 To 15) As Variant
dim ArrayCalc as String
dim Qtr as string
Qtr = "2005Q3"
dim Up as string
Up = "201001"
ArrayCalc = "=SumProduct( If (Quarters= " & Qtr & ",1,0), If (UpdateMonth= " & Up & ",1,0), Data)"
X(i,n).formulaarray = ArrayCalc