Using variable in excel formula

Question

I have a formula in my VBA code in which I would like to embed a variable. I defined an integer but am unsure how to add it to my formula.

 Dim Formula as Variant
 Dim Stability as Integer
 Stability = 0.5
 Formula = Application.WorksheetFunction.AverageIfs(range("C:C"), range("C:C"), ">=10", range("C:C"), "<=15", range("F:F"), "<=Stability")

At the end of the formula I want to include my Stability variable. What is the correct syntax for doing so?

May i ask the purpose of your code? why don't you just construct the formula and stick it to the cell value so excel will calculate for you? like .range("A2).Formula = "=Average('C:C')" ? — Krish
– Krish, Commented Aug 28, 2014 at 18:54
Note that an Integer is a, we'll, integer, and cannot hold a floating point value. Use Double instead. — chris neilsen
– chris neilsen, Commented Aug 28, 2014 at 19:12

user2140173 · Accepted Answer · 2014-08-28 18:15:28Z

2

try

Formula = ..., "<=" & Stability)

answered Aug 28, 2014 at 18:15

user2140173

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

3 Comments

MalusPuer Over a year ago

I get an error when doing that. "Unable to get the AverageIfs property of the WorksheetFunction class.

user2140173 Over a year ago

@MalusPuer make sure you understand how =AVERAGEIFS works

MalusPuer Over a year ago

The code works when I have .5 where stability is in the formula. It just wont let me put a variable there

Collectives™ on Stack Overflow

Using variable in excel formula

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related