VBA Evaluate to result an array
Results 1 to 5 of 5

Thread: VBA Evaluate to result an array

  1. #1
    New Member
    Join Date
    Sep 2014
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Evaluate to result an array

    Hello,

    I've got an array Countifs function that should return array as result:
    Code:
    Dim ws_Test As Worksheet
    Dim Result_Array As Variant
    
    
    Set ws_Test = ThisWorkbook.Worksheets("Sheet1")
    
    
    Result_Array = ws_Test.Evaluate("COUNTIFS(I1,A:A,I2,B:B,I3,C:C,I4,D:D,I5,E:E,I6,F:F)")
    But instead Result_Array becoming array it becomes double from "non array" function.

    However if I do:
    Code:
    Result_Array = ws_Test.Evaluate("SUM(COUNTIFS(I1,A:A,I2,B:B,I3,C:C,I4,D:D,I5,E:E,I6,F:F))")
    The Result will be SUM from an Array...

    Does this mean that Evaluate cannot return true result of an formula(e.g. array) but the value result as the formula in cell would do?

    Best Regards,
    Barb

  2. #2
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,552
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA Evaluate to result an array

    It does return an array as the result.
    Looking for opportunities

  3. #3
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    33,725
    Post Thanks / Like
    Mentioned
    48 Post(s)
    Tagged
    6 Thread(s)

    Default Re: VBA Evaluate to result an array

    Evaluate often needs a bit of coaching to return an array - you could use:

    Code:
    Result_Array = ws_Test.Evaluate("INDEX(COUNTIFS(I1,A:A,I2,B:B,I3,C:C,I4,D:D,I5,E:E,I6,F:F),)")

  4. #4
    New Member
    Join Date
    Sep 2014
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Evaluate to result an array

    Love you! thank you

    Quote Originally Posted by RoryA View Post
    Evaluate often needs a bit of coaching to return an array - you could use:

    Code:
    Result_Array = ws_Test.Evaluate("INDEX(COUNTIFS(I1,A:A,I2,B:B,I3,C:C,I4,D:D,I5,E:E,I6,F:F),)")

  5. #5
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,552
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    3 Thread(s)

    Default Re: VBA Evaluate to result an array

    Must be going mad because it produced an array of values for me without index.
    Looking for opportunities

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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