View Full Version : How to use Countif in VBA

05-18-2006, 03:18 AM

Can any one tell me that how to use Countif function in VBA (Macro Programming). I need to count the no.of rows with specifying the criteria. I can do this using for loop and but just want to know whether is it possible to use simple function rather than using loop and conditional statements.

Thanks in Advance !

Warm regards,

05-18-2006, 04:41 AM
There might be other ways - I'm not entirely sure what you want to do - but you can use Application.CountIf(Range("a1:a20"),"=5") for example

05-18-2006, 04:41 AM
Moved to Excel Help forum...

05-18-2006, 05:08 AM
Thanks for the reply Tony.. but when i put this formula it is returning "0" value. my code is goes like this...
Sheets("Daily Call Report").Range("C8").Select
ActiveCell.FormulaR1C1 = Application.CountIf(Range("H1:H400"), "=17/05/2006")
i want to count how many rows r there in a sheet which contains the date "17/05/2006".
Thanks in Advance !

05-18-2006, 05:31 AM
You're better off using CountA for that...
Option Explicit
Sub Try()
With Sheets("Daily Call Report")
.Range("C8") = Application.CountA(Range("H1:H400"), "=17/05/2006") - 1
End With
End Sub

05-18-2006, 07:08 AM
probably a silly question but why do you want to use VBA to put the formula result in the cell rather than putting the formula itself in the cell?

05-22-2006, 04:45 AM
Yes it is a silly question.. i know.. but the code what i sent is a just part of it. and more over this report is very big one.. and it includes only the count of no.s. so i thouhgt of creating a macro. Any way ... thanks for your help. i wil get back to you.. once i use this code in my program.