Using for loop counter as a cell reference

[Bert Onstott]

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

 

[OssieMac]

Hi Bert,

Basically the code is OK provided that you do not change worksheets and it
applies to the activesheet. The error 1004 suggest this is not the case.

Try coding like the following.
Set Old_Info = Sheets("Sheet1").Range(OldCell, OldCell.End(xlToRight))

If the above does not work then can you post the code for Define_Lists.

Also it is always good practice to specify the worksheet in your code. That
way you should never have problems with the incorrect worksheet being the
activesheet.

 

[Per Jessen]

Hi

I suppose the Old_List variable is assigned a range in the Define_Lists
macro, so add this to the Compare_Lists macro before the set statements:

msgbox Old_List.Address ' For test only

I suspect that you declare the Old_List variable inside the Define_List
macro, then the variable is only valid in this module. Try to declare this
variable at the very top of the module, then the variable can be used in all
macros in the module.

Hopes this helps.
....
Per