VBA Evaluate to result an array

[Barbatian]

Hello,

I've got an array Countifs function that should return array as result:

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:

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

 

[steve the fish]

It does return an array as the result.

 

[RoryA]

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

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),)")

 

[Barbatian]

Love you! thank you

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

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),)")

 

[steve the fish]

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