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 a
Ldb = "LAFQ403"
rwct = 568
counter = 0

TIA
Mike

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

--
Regards,
Tom Ogilvy

"Mike Fogleman" wrote in message
...
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 a
Ldb = "LAFQ403"
rwct = 568
counter = 0

TIA
Mike

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?


--
Regards
Frank Kabel
Frankfurt, Germany

Mike Fogleman wrote:
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 a
Ldb = "LAFQ403"
rwct = 568
counter = 0

TIA
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.
Mike Fogleman wrote in message
...
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 a
Ldb = "LAFQ403"
rwct = 568
counter = 0

TIA
Mike