Results 1 to 3 of 3

Thread: Is .FormulaArray limited to Range?

  1. #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]

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Apr 2005
    Show us all of the code.
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    [vba]Dim i, n As Integer

    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]
    Last edited by skulakowski; 07-25-2011 at 01:31 PM. Reason: typo

Posting Permissions

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