VBA- Excel COUNTIF

[Evening]

I am running the VBA code from sheet1.

Selecting another sheet FMA, and finding the last row .
I want to define variable CN, where it shows
the number cases in fields in Column Y, from row 11 to LastRow, where the value is greater then 0.
How can I define the variable CN?.

Select Sheet1
.
.
.
Select sheet(FMA)
lastRow = ActiveSheet.Range("B65536").End(xlUp).Row
ActiveSheet.Range(C11).Select
CN = "=COUNTIF(Y11:Y" & lastRow & "), "">0"" )"


When I try to determine the value of CN,
the CN i am getting is "=Countif(Y11:Y403,">0")"
which is a formula string, but not the real number of cases.
I know in Excell the formula it calculates the value, but
in VBA how can I specify it?
Thanks for help

 

[Loomah]

you can access worksheet functions directly from vba in the following way
CN = worksheetfunction.countif(range("Y11:Y" & lastRow), ">0")


;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[PHV]

You may use the Application.WorksheetFunction.CountIf function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886