# excel vb macro code for countif()



## yaec (Jul 26, 2005)

I'm trying to code a macro with (among other things) a section using countif(). For now let's just assume that the range is fixed, say C3:C43. The criteria needs to reference 2 other cells. I want the countif to equal 

(the # of values <= the # in cell(31)) - (the # of values <= the # in cell(30)), both in a different column.

I can select the range but I don't know excel's code for using countif() in a macro. Here's what I tried so far:

Range("C3:C43").Select
a = Selection.CountIf("<=m-<=p")

where m=# in cell(31) and p=# in cell(30)
I get an Object doesn't support this method error.

How can I get there? TIA


----------



## XL Guru (Aug 30, 2003)

Welcome to TSG. 

A few guys around here do proper VBA, I'm not one of them ; they may drop by later.

I do know that (a) you *don't* need to actually select the range to work on it [that just slows things down] (b) you *do* need to use the syntax *WorksheetFunction.CountIf* ...

See if this'll work for you:

Sub Test()
a = WorksheetFunction.CountIf(Range("C3:C43"), "<=" & Range("A31")) - _
WorksheetFunction.CountIf(Range("C3:C43"), "<=" & Range("A30"))
MsgBox a
End Sub

Rgds,
Andy


----------



## yaec (Jul 26, 2005)

Ah, that works great. Once you know the syntax of these commands things go a lot smoother.  

Thanks. :up:


----------



## Zack Barresse (Jul 25, 2004)

If you don't have the VBA help files installed, I highly recommend doing so. They have all (most..) correct syntax's and generally have examples of each. They are far underated and a great resource to use. If not installed during application installation, the cd will be required for additional installs.

Take care!

(Hiya Andy!!)


----------



## XL Guru (Aug 30, 2003)

firefytr said:


> (Hiya Andy!!)


Hi back/thanks for not stomping all over my kludge code.


----------



## Zack Barresse (Jul 25, 2004)

XL Guru said:


> Hi back/thanks for not stomping all over my kludge code.


LOL! (I'm not that harsh, am I?) It's good stuff!


----------

