Forum

How to fix the Cont...
 
Notifications
Clear all

How to fix the Conta.Più.Se error

5 Posts
4 Users
0 Reactions
86 Views
(@a-maurizio)
Posts: 214
Reputable Member
Topic starter
 

Hello everybody
My problem is this:
On a sheet of excel in the column (D3: D100) I insert dates not only imprinted on the year of various genes.
Then in cell (J8) I enter this formula:

=CONTA.PIÙ.SE($D$3:$D$42;">=1930";$D$3:$D$42;"<=1970")

And so far so good.
But now I would also like to insert this formula by writing everything using the vba
I tried to write it like this:

sheets("es2_edifici").Range("K8").Formula= sheets("es2_edifici").Range("K8").Formula= "=CONTA.PIÙ.SE($D$3:$D$42;">=1930";$D$3:$D$42;"<=1970")"

But in doing so he continues to give me error; Why ?
And how can I solve this problem Thank you

 
Posted : 06/07/2021 12:36 pm
Alan Sidman
(@alansidman)
Posts: 223
Member Moderator
 

You need to preface the countifs function in VBA with 

Application.WorksheetFunction

See this link for further understanding.

https://www.automateexcel.com/vba/vba-countif-and-countifs-functions/

 
Posted : 06/07/2021 7:04 pm
(@catalinb)
Posts: 1937
Member Admin
 

If you are trying to write the cell formula from VBA, you have to duplicate the double quotes within the formula string:

.Range("K8").Formula= "=Countifs($D$3:$D$42;"">=1930"";$D$3:$D$42;""<=1970"")"

The easiest way to get the syntax right is to record a macro while typing that formula in a cell.

Why is the part in red below twice? Should be once, remove the duplicate.

sheets("es2_edifici").Range("K8").Formula= sheets("es2_edifici").Range("K8").Formula= "=CONTA.PIÙ.SE($D$3:$D$42;">=1930";$D$3:$D$42;"<=1970")"

 
Posted : 06/07/2021 10:48 pm
(@debaser)
Posts: 837
Member Moderator
 

Also, you need to use FormulaLocal if you want to use the Italian syntax and function names.

 
Posted : 07/07/2021 5:40 am
(@a-maurizio)
Posts: 214
Reputable Member
Topic starter
 

Infinite Thanks to All of You
Now I have been able to solve my problem by following the directions of: Alan Sidman
You are all fantastic
Infinite grace
E W Italy Football
Although to tell the truth, in my heart there is always Real Madrid
Hello everyone from A.Maurizio

 
Posted : 07/07/2021 12:56 pm
Share: