I didn't run the code but what popped out at me was no end
if after the if's in the code. try adding that?
-----Original Message-----
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

.

If that were a problem, it wouldn't compile. I think he's using single-line IF
statements -- they are just wrapping in the message.

I thought he implied it goes into a [endless] loop, and I can't see why. His 3
loops are all For/Next, and each has a finite ending point (row 37, 81 cells
in a row on sheet1 (G:CI), column 18). I wonder if, because of the use of
collections, it is just very slow and he *thinks* it's in a loop.



On Wed, 28 Jul 2004 10:51:25 -0700, "Frank Stone"
wrote:

I didn't run the code but what popped out at me was no end
if after the if's in the code. try adding that?
-----Original Message-----
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

.