+ Reply to Thread
Results 1 to 3 of 3

Array Formula using Application.WorksheetFunction

  1. #1
    Registered User
    Join Date
    07-18-2007
    Posts
    9

    Array Formula using Application.WorksheetFunction

    Hey folks,

    So I'm attempting to use an array function in VBA. Well, attempting- As an example, here's what I'd enter into a cell:

    =SUM((A2:A25="North")*(C2:C25>VALUE("7-Apr-2005"))*(E2:E25="2-Door"))+SUM((A2:A25="North")*(C2:C25>VALUE("7-Apr-2005"))*(E2:E25="4-Door"))
    Followed by cntrl-shift-enter.
    That'd count all instances of North, After April-7-2005, with 2-Door OR 4-Door. Great.

    But to get that in VBA....

    Range("D30") = Application.WorksheetFunction.Sum(???)

    Any ideas?

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Array Formula using Application.WorksheetFunction

    You would need to use .FormulaArray however you can achieve the same thing using SUMPRODUCT which does not require Array (though is roughly the same in terms of performance)

    Please Login or Register  to view this content.
    If you're saying you want to only return the value then either

    Please Login or Register  to view this content.
    or use Evaluate

    Please Login or Register  to view this content.
    Last edited by DonkeyOte; 09-04-2009 at 03:59 PM.

  3. #3
    Registered User
    Join Date
    07-18-2007
    Posts
    9

    Re: Array Formula using Application.WorksheetFunction

    Ah perfect, can't believe I haven't seen SUMPRODUCT before, it's really useful. Thanks for the help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1