Using for loop counter as a cell reference

[Bert Onstott]

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

 

[JLGWhiz]

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]

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