I'm working on an Excel VBA Macro.
I must write a formula inside a cell.
The formula I'm referring to is CONTA.SE(). It behaves in the same way as COUNTIF() does. I guess that it is the COUNTIF() version for Excel in italian.
To be more precise the formula I want to implement is:
=CONTA.SE(Report!E:E;"<20")
so that it works on cells values from E2 to the last non-empty cell.
I guess I'm having trouble because of the double quotes.
I've tried many solutions as, for example:
Cells(5, 2).Formula = "=CONTA.SE(Report!E2:E" & rowCount & ";" & Chr(34) & "<20" & Chr(34) & ")"
or:
Cells(5, 2).Formula = "=CONTA.SE(Report!E2:E" & rowCount & ";" & """<20""" & ")"
The error message I got is:
run-time Error 1004:
Error defined by the Object or the Application.
Can anyone, please, tell my how write it correctly?
.Formula uses the english version of the formulas. If you want to use the localized formula (eg italian) you must use .FormulaLocal.
So either localized:
Cells(5, 2).FormulaLocal = "=CONTA.SE(Report!E2:E" & rowCount & ";" & Chr(34) & "<20" & Chr(34) & ")"
or english
Cells(5, 2).Formula = "=COUNTIF(Report!E2:E" & rowCount & "," & Chr(34) & "<20" & Chr(34) & ")"
Note that the localized (italian) form only works when used in an italian Excel, but the english version would work on any Excel. So using the english version in VBA is recommended. Using the english formula in VBA will automatically convert it into the correct localization form in the cell (the user will not notice any difference).
Also note that the english separator is , while the italian (and most european) separator is ;.