Consulting

Results 1 to 7 of 7

Thread: Formula Array in VBA

  1. #1
    VBAX Regular
    Joined
    Jul 2005
    Posts
    34
    Location

    Formula Array in VBA

    Can someone tell me how you go about using the formula array in VBA? I have the following code:

    Range("N10").FormulaArray = _
            "=SUM((Miles>=R9C)*(Miles<R9C[1])*(Depth<=RC13)*(Depth>R[1]C13))"

    I keep getting an error message stating that "Unable to apply Array Formula" and "Invalid name error"? Any suggestions on how to set the array formula in VBA

    Thanks

  2. #2
    VBAX Mentor Justinlabenne's Avatar
    Joined
    Jul 2004
    Location
    Clyde, Ohio
    Posts
    408
    Location
    FYI, cross-posted here
    Justin Labenne

  3. #3
    VBAX Regular
    Joined
    Jul 2005
    Posts
    34
    Location
    Sorry again for some reason I can't seem to post the whole line of the code: Let me try this again:\
    Range("N10").FormulaArray = _ 
    "=SUM((Miles>=N$9)*(Miles<O$9)*(Depth<=$M10)*(Depth>$M11))" 
    Selection.AutoFill Destination:=Range("N10:N16"), Type:=xlFillDefault
    I get the following error message:

    Runtime error '1004'
    Unable to set the FormulaArray property of the Range Class

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Worked for me.

    I did force it by merging N10 and M10. Do you have merged cells?
    ____________________________________________
    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

  5. #5
    VBAX Regular
    Joined
    Jul 2005
    Posts
    34
    Location
    no I don't have any merged cells.

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by goldie12
    no I don't have any merged cells.
    Have you tried to create the formula in Excel in cel;l N10, and found any problems?
    ____________________________________________
    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

  7. #7
    VBAX Regular
    Joined
    Jul 2005
    Posts
    34
    Location
    It works fine now. I had forgot to define my labels. I had to go to the Insert menu then Name then define to input my OFFSETS for the two columns.

Posting Permissions

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