SUMIFS in VBA

[Lectricman]

Can anyone explainto me what is wrong with this Formula.
=SUMIFS(F760:F841,B760:B841,"A",I760:I841,"B",J760:J841,"C",K760:K841,"D")
On a worksheet it works without issue. When I try it in VBA it gives the following error.

Compile Error:
Expected: List Separator or).

The colon between F760 and F841 is hi-lighted.


Many Thanks in advance

 

[Fluff]

Select the cell with formula, then in the Vb editor put this in the Immediate window (Ctrl G will bring it up if needed)

?Replace$(activecell.formular1c1, """", """""")

Then copy & paste the result into your macro

Picked this up from RoryA

 

[JoeMo]

Can you post a snippet of the code to show exactly how you used this formula in VBA?

 

[Eric W]

A couple ways to call SUMIFS from VBA:

    x = Evaluate("SUMIFS(F760:F841,B760:B841,""A"",I760:I841,""B"",J760:J841,""C"",K760:K841,""D"")")
    
    y = WorksheetFunction.SumIfs(Range("F760:F841"), Range("B760:B841"), "A", Range("I760:I841"), "B", Range("J760:J841"), "C", Range("K760:K841"), "D")

Note the double quotes in the interior of the first line. Fluff's Replace trick will do that for you.

 

[Lectricman]

A couple ways to call SUMIFS from VBA:

    x = Evaluate("SUMIFS(F760:F841,B760:B841,""A"",I760:I841,""B"",J760:J841,""C"",K760:K841,""D"")")
    
    y = WorksheetFunction.SumIfs(Range("F760:F841"), Range("B760:B841"), "A", Range("I760:I841"), "B", Range("J760:J841"), "C", Range("K760:K841"), "D")

Note the double quotes in the interior of the first line. Fluff's Replace trick will do that for you.

Thank you so much. The top one did it for me.

 

[Eric W]

Glad we could help.