Can anyone clarify for me why the below doesn't work?

I get an error 1004 on

Set Old_Info = Range(Oldcell, Oldcell.End(xlToRight))

so I obviously don't know how to define that range.

If I comment out those few lines, the Sub executes without error. Of
course, it doesn't do anything, but it does loop through.


Sub Compare_Lists()

Dim Oldcell As Range
Dim Newcell As Range
Dim Old_Info As Range
Dim New_Info As Range

Dim Found_match As Boolean

Call Define_Lists

Application.ScreenUpdating = False

' First loop through the old list to see if each ACBL number
' in the old list is found in the new list.
'
' If it is, check to see if the directory information is the same.
' If it is not, write the SQL update statements to update the database.
' If it is, exit the loop
'
' If the number is not found, write the SQL statements to delete that record
' from the database.

For Each Oldcell In Old_List
For Each Newcell In New_List
Found_match = False
If Oldcell = Newcell Then
' Found the same ACBL number in both lists
Found_match = True
' Compare directory info for Oldcell and NewCell
Set Old_Info = Range(Oldcell, Oldcell.End(xlToRight))
Set New_Info = Range(Newcell, Newcell.End(xlToRight))
If Old_Info = New_Info Then
Exit For
' else
' write sql update statements to update directory info in database
End If
End If
Next Newcell
If Found_match = False Then
' A number in old list was not found in new list, so
' write SQL commands to delete old number from database
End If
Next Oldcell

' The above will miss anyone who appears in the new list who didn't appear
in the old.
' so loop through in the opposite order to find those.
' If a number appears in the new list but not in the old, write the SQL
statements to add that
' record to the database.

For Each Newcell In New_List
For Each Oldcell In Old_List
Found_match = False
If Oldcell = Newcell Then
' Found the same ACBL number in both lists
Found_match = True
' exit for
End If
Next Oldcell
If Found_match = False Then
' A number in the new list was not found in old list, so
' write SQL commands to add the new number to the database.
End If
Next Newcell

End Sub


--
Bert Onstott
3303 N. Sutton Sq
Stafford, TX
281-494-9644