Using Excel Formula in VBA

Question

Currently I have the following formula working perfectly when entered directly into the excel sheet. =AVERAGEIFS(A:A,A:A,">="&10,A:A,"<="&11)

However, when I attempt to exectue it in VBA it says there is a formatting error.

Formula = Application.WorksheetFunction.AverageIfs(A:A,A:A,">="&10,A:A,"<="&11)

Is there any way to fix this?

Alex · Accepted Answer · 2014-08-14 14:42:22Z

4

You need to use Range() to define ranges in VBA:

Formula = Application.WorksheetFunction.AverageIfs(Range("A:A"), Range("A:A"), ">=10", Range("A:A"), ">=11")

answered Aug 14, 2014 at 14:42

Alex

1,6321 gold badge12 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1