CODEWITHSUNDEEP
excelvba
Pierre44
Pierre44

Reputation: 1741

Using a formula in a variable

I have the following formula: 

Range("ZZ1").Formula = "=SUMPRODUCT(--(A3:A" & LastRow & "<>""""))"

Now I wish I could use it directly to get the value without first putting it on my sheet. Is there a way to get it inside a variable directly as below? 

Dim x as long
x = "=SUMPRODUCT(--(A3:A" & LastRow & "<>""""))"

Upvotes: 0

Views: 25

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

You can use Application.CountIf:

x=Application.Countif(Range("A3:A" & LastRow),"<>"))

Upvotes: 0

Rory
Rory

Reputation: 34045

You can use Evaluate like this:

Dim x as long
x = Application.Evaluate("SUMPRODUCT(--(A3:A" & LastRow & "<>""""))")

This will evaluate in the context of the active sheet. You can also use the Worksheet.Evaluate method to evaluate in the context of a specific sheet.

One caveat: the formula string cannot be longer than 255 characters, but that does not appear to be an issue here.

Upvotes: 1

Related Questions