Can anyone tell me why the below doesn't work?

I get an error 1004 on

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

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

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

This might work:

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

You can always use message boxes to check the value of the range objects
like this.
Just put:

MsgBox Old_Info

immediately after the Set statement. If it gives you something like

"$A$1:$A$25"

Then you know it is OK. You can then delete your message box and go on.




"Bert Onstott" wrote in message
...
Can anyone tell me why the below doesn't work?

I get an error 1004 on

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

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

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

Unfortunately, it gets the error when executing the Set statement, so it
never gets to the MsgBox statement.

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


"JLGWhiz" wrote:

This might work:

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

You can always use message boxes to check the value of the range objects
like this.
Just put:

MsgBox Old_Info

immediately after the Set statement. If it gives you something like

"$A$1:$A$25"

Then you know it is OK. You can then delete your message box and go on.




"Bert Onstott" wrote in message
...
Can anyone tell me why the below doesn't work?

I get an error 1004 on

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

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

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