Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using for loop counter as a cell reference
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Do While Loop using time as the counter | Excel Programming | |||
For Next loop faster with counter after Next? | Excel Programming | |||
Counter variable in For Loop | Excel Programming | |||
Should I use Do-While loop for my record counter? | Excel Programming | |||
On Screen Loop Counter | Excel Programming |