MsOf07 [RESOLVED] VBA CountIf problem (multiple worksheets)

I searched, but the threads I found didn't help much. Hopefully I didn't miss an obvious thread.

So basically I have two sheets. One sheet (Sheet A) has a column of names and then various attributes related to these names in the other columns. The second sheet (B) has a column of names that is a subset of the names in the first sheet. Right now I need to determine how many times a name in Sheet B appears in Sheet A (a range within Sheet A to be more precise). I'm using CountIf to do this, but I need the criteria to be a variable-- is even allowed? I have the following code

Code:

For i = 5 To 5 ' assigns current player to a string ' check # of teams player appeared on in 2010 player = Cells(i + 1, 2).Value teamnum = WorksheetFunction.CountIf(Sheets("2010-2012 Metrics").Range("C1045:C1555"), Cells(i + 1, 2).Value) MsgBox player MsgBox teamnum Next i

The for loops just refers to the # names in Sheet B. I've set it to name 5 so I can test the # of ocurrences for this particular person. It keeps returning 0; is that b/c I can't use "Cells(i + 1, 2).Value"? I created a string variable for this value as well, initially in hopes of doing something like

Code:

teamnum = WorksheetFunction.CountIf(Sheets("2010-2012 Metrics").Range("C1045:C1555"), player)

but that failed

i suspect that my error is syntactical b/c even when i put a character string (i.e. a name), that should return a non-zero value, i still get 0. So say "John Doe" is in the list. even when I try

Code:

teamnum = WorksheetFunction.CountIf(Sheets("2010-2012 Metrics").Range("C1045:C1555"), "John Doe")

the MsgBox still reads 0 for teamnum

however, when i use the following code

Code:

teamnum = Sheets("2010-2012 Metrics").Range("C1045").Value

it provides the corect name in cell C1045 of the corresponding sheet. So I guess I'm using the WorksheetFunction.CountIf command incorrectly. I appreciate any help

P.S. I thought it was possible that the names had different #'s of spaces in them and thus were never being seen as "the same," so I trimmed all the names. Then I applied the match function between the two sheets and it in fact works so I'm positive that the (most) names, and all the names I have tried so far, should come up at least once