help needed with Countif....

[Eric @ BP-EVV]

I'm trying to use the following function in VBA:

=COUNTIF(B2:B2005,B14)

Where B2 will never change, B2005 is determined by the following code:

Application.WorksheetFunction.CountA(Worksheets("working
data").Range("A1:A65536"))

and B14 is the column B and the current row of a table.

I have tried :

iteration = Application.WorksheetFunction.CountIf(Worksheets("working
data").Range("A1:K" & rcount + 1), "B" & rowx)

where rowx is predefined as 2, and incremented by 1 each time a go through
the loop....but al I get back on this is a big - fat - zero.

What am I doing wrong ?

Thanks !

 

[Dave Peterson]

maybe...

with worksheets("working data")
iteration = Application.CountIf(.Range("A1:K" & rcount + 1), _
.range("B" & rowx))
end with

 

[Eric @ BP-EVV]

Thanks for the suggestion Dave - I wish I had better results...I got "Object
doesn't support this property or method" as an error.

Anyone else have other ideas ?

Thanks !

 

[Dave Peterson]

I'd guess that there was something wrong with rcount or rowx.

But that's a guess. I didn't see how you set those variables to anything.

 

[Eric @ BP-EVV]

I solved it - here's how the code looks now (tested and works):

iteration = Application.WorksheetFunction.CountIf(Worksheets("working
data").Range("B1:B" & rcount), Worksheets("working data").Range("B" & rowx))

I'm glad I worked this on out, the bruise on my forehead from hitting the
wall too many times was getting rather large !