Count instances of text on a different worksheet

[raeannb]

Hello! My problem may possibly (??) be solved with a CountIf function or similar, but I'm trying and failing miserably. Here it is:

I have two worksheets set up, one called "Sites" and the other called "IDs." On "Sites" is a column of 5-digit numerical (but formatted as text) site IDs, and on "IDs" is a range containing 9-digit customer IDs of the format #####-###, where the first five digits correspond to a site ID. What I would like to do is loop through column A of "Sites" (which contains the Site IDs) and have my program count the number of times a customer from each site occurs in the given range on "IDs." I then would like the count to be placed alongside the site number on "Sites" in column B.

So far I have:


Sub CountSites()
With Worksheets("Sites")
x = .Range("A65536").End(xlUp).Row - 5
For i = 1 To x
.Range("B" & i + 5).Value = .CountIf(Worksheets("IDs").Range("B6:P36") _
, Worksheets("Sites").Range("A" & i + 5).Value) **** ERROR
Next i
End With
End Sub

I'm getting an "Object doesn't support this property or method" error at the CountIf function. Any clue what I'm doing wrong?

Thank you!!!!

 

[cstimart]

Comment out (put an apostrophe before) "**** ERROR"

See how that works.

 

[raeannb]

Comment out (put an apostrophe before) "**** ERROR"

See how that works.

Smart aleck! I wish...

 

[raeannb]

I figured it out! For anyone who wants to know, I gave up on VB and just put this formula in column B:


=SUMPRODUCT((LEFT('IDs'!$A$6:$P$38, 5)= A6)+0)

Ta da! Happy counting, everyone...