VBA Excel implement COUNTIF function

Question

I am trying to implement a "COUNTIF()" function in my vba excel application. I know how to do this programatically but I want specifically to implement this as a formula so that later changes in the sheet will hold. This is the problematic line:

ActiveSheet.Cells(3, 20).FormulaR1C1 = "=COUNTIF(R11C7:R12C7;"">0"")"

It results in the following error:

Run-time error '1004': Application-defined or object-defined error

Is your computer's regional system setting for List Separator a semi-colon (less common) or a comma (predominant)? — user4039065
– user4039065, Commented Jan 26, 2015 at 11:58
In the sheet itself, a ; suffices. However, I tried to use a , in the formula and it worked. — hasdrubal
– hasdrubal, Commented Jan 26, 2015 at 12:01
VBA assumes US formatting unless otherwise specified (which you could do here using FormulaR1C1Local) — Rory
– Rory, Commented Jan 26, 2015 at 12:04

Rory · Accepted Answer · 2015-01-26 12:30:29Z

2

VBA defaults to US formatting unless otherwise specified - which you could do here using FormulaR1C1Local - so you need to use a comma separator, not a semicolon.

answered Jan 26, 2015 at 12:30

Rory

34.2k5 gold badges40 silver badges42 bronze badges

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

Collectives™ on Stack Overflow

VBA Excel implement COUNTIF function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related