If I understand the problem correctly, I think using collections for this is
not necessary and probably slows it down. Since you have put this in a
Worksheet_Change event macro, speed is a concern. The only property of the
collection(s) that you use is the .Count, so just set up numeric variables to
use as the counters.

You want to count the number of occurrences of each item on sheet1 in the list
on sheet2. I figured that the range on sheet2 is J5:J22. Is this correct?
That's what I used, and I used COUNTIF to get the count rather than your

For Ct = 1 To 18

loop. If I don't have the range right, you can correct it. Please let me know
if this does what you want.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Long
Dim Found As Long
Dim NotFound As Long
Dim R As Long
Dim WS3Row As Long
Dim Rng1 As Range
Dim Rng2 As Range
Dim WS1 As Worksheet
Dim WS3 AS Worksheet

Set WS1 = Worksheets("Sheet1")
Set WS3 = Worksheets("Sheet3")

Set Rng1 = WS1.Range("G1:CI37")
Set Rng2 = Worksheets("Sheet2").Range("J5:J22")

WS3Row = 5 'data on sheet3 starts at row 5

With Rng1
'process odd-numbered rows beginning with row 3
For R = 3 To .Rows.Count.Row Step 2
'initialize the counters for this row
Found = 0
NotFound = 0
For C = 1 To .Columns.Count
F = Application.COUNTIF(Rng2, .Cells(R, C).Value)
If F then
Found = Found + F
Else
NotFound = NotFound + 1
End If
Next C

WS1.Cells(R - 1, 4).Value = Found
WS1.Cells(R, 4).Value = Notfound
WS3.Cells(WS3Row, 6).Value = Found + NotFound
WS3Row = WS3Row + 1
Next R
End With
End Sub

On Wed, 28 Jul 2004 07:48:04 -0700, "Erik"
wrote:

With my limited knowledge and abilities I tried to modify some code I was
already using. But when I changed it, it stoped working properly. I am
trying to compare lists of names in rows in sheet1 to a list in a column in
sheet2 and return the count of how many of the names in each row in sheet1 are
in the list in sheet2 and how many are not in the list. Also I want the total
count of individual names in each row in sheet1. It almost works, but it gets
stuck in a loop. Below is what I have muddled through. If anyone has any
suggestions, I would really appreciate them.
Erik
Private Sub Worksheet_Change(ByVal Target As Range)
Dim aNames As Collection
Dim bNames As Collection
Dim c As Range
Dim rng As Range
Dim iCt As Integer
Dim iRow As Integer
Dim Ct As Integer


On Error Resume Next

For iRow = 3 To 37 Step 2
Set aNames = New Collection
Set bNames = New Collection
Set rng = Sheets("sheet1").Range("G" & iRow & ":CI" & iRow)
For Each c In rng
Debug.Print c.Address
For Ct = 1 To 18
If c.Value = Sheets("sheet2").Cells(Ct + 4, 10) Then aNames.Add
c.Value, c.Value
Next Ct
If aNames.Count = 0 Then bNames.Add c.Value, c.Value
Next c

Sheets("sheet1").Cells(iRow - 1, 4) = aNames.Count
Sheets("sheet1").Cells(iRow, 4) = bNames.Count
Sheets("sheet3").Cells((iRow - 1) / 2 + 4, 6) = aNames.Count +
bNames.Count

Set aNames = Nothing
Set bNames = Nothing

Next iRow

End Sub

I get a compile error Invalid Qualifier for .Count in the following line.

For R = 3 To .Rows.Count.Row Step 2

Erik

Myrna,

Also, if I understand your code correctly, I don't think it is going to give me the numbers I'm looking for.
Example:
Row3 contains the following names. James, Mike, Mike, Jeff, James. Where Mike and Jeff are in the list in sheet2 and James is not. The code should yeild 2 names in the list, 1 name not in the list and 3 people total.

Erik