Syntax for WorksheetFunction

[Mike Fogleman]

Sub CntIf()
Dim Ldb, rwct, counter
Dim myrng As Range

Sheets("Node Data").Activate
If Range("B2").Value = "" Then
Cells(1, counter + 2).Value = "Total Leaks per " & Ldb
Set myrng = Sheets(Ldb).Range("$B$2:$B$" & rwct)

Range(Cells(2, counter + 2), Cells(85, counter + 2)) =
Application.WorksheetFunction.CountIf(myrng, "$A$2:$A$85") 'This does not
work
Range(Cells(2, counter + 3), Cells(85, counter + 3)).Formula =
"=COUNTIF(LAFQ403!$B$2:$B$568,$A$2:$A$85)" 'This works

End If
End Sub

How do I get the variables to work in the WorksheetFunction?
The variable values at these lines are:
Ldb = "LAFQ403"
rwct = 568
counter = 0

TIA
Mike

 

[Tom Ogilvy]

the second argument of Countif should be a single value. When entered in
the worksheet, you are using implicit intersection, so it is only getting
one value. If you moved the location of your formulas to start in row 86 to
168, it wouldn't work Your easiest fix would probably be just to loop

set myrng = Range("LAFQ403!$B$2:$B$568")
for rw = 2 to 85
Cells(rw, counter + 2) =
Application.WorksheetFunction.CountIf(myrng, Cells(rw,1))
Next

 

[Frank Kabel]

Hi Mike
the problem is that the second parameter of COUNTIF should be only a
single value. e.g.
Application.WorksheetFunction.CountIf(myrng, "$A$2")

As I'm not so sure what you want to achieve you may have to loop
through this second range to get all your counts?

 

[Mike]

Thanks Tom & Frank. I wasn't aware that I was violating an implicit
intersection by using a criteria range > than 1 cell. The loop that Tom used
works fine in this instance.