# Thread: Is .FormulaArray limited to Range?

1. ## Is .FormulaArray limited to Range?

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.

[vba]=SumProduct( if (Quarters="2005Q3",1,0), if (UpdateMonth=201001,1,0), Data)[/vba]
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?

[vba]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.[/vba]  Reply With Quote

2. Show us all of the code.  Reply With Quote

3. [vba]Dim i, n As Integer
i=1
n=1

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[/vba]  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•