Sandeep Warrier
Well-known Member
- Joined
- Oct 31, 2008
- Messages
- 2,679
Hey All,
I have all but broken my head over this. And I can't seem to find where the problem is!!!
This is NewRng
Excel 2003
This is IniRng
Excel 2003
I am trying to compare IniRng with NewRng to see if there are any changes. As can be seen, there are 2 changes (the 1st name and the 5th name)
The code below detects both changes but RemRng is populated with only the 1st change. Can anyone see where I am going wrong? I've tried Union and Application.Union but get same results.<font face=Courier New> <SPAN style="color:#00007F">For</SPAN><SPAN style="color:#00007F">Each</SPAN> rCell<SPAN style="color:#00007F">In</SPAN> IniRng<br> <SPAN style="color:#00007F">If</SPAN> IsError(WorksheetFunction.Match(rCell.Value, NewRng, 0))<SPAN style="color:#00007F">Then</SPAN><br> <SPAN style="color:#00007F">If</SPAN> RemRng<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN><br> <SPAN style="color:#00007F">Set</SPAN> RemRng = rCell<br> <SPAN style="color:#00007F">Else</SPAN><br> <SPAN style="color:#00007F">Set</SPAN> RemRng = Union(RemRng, rCell)<br> <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#00007F">Next</SPAN> rCell<br> RemCount = RemRng.Rows.Count</FONT>
I have all but broken my head over this. And I can't seem to find where the problem is!!!
This is NewRng
Excel Workbook | |||
---|---|---|---|
A | |||
12 | LUNDBURG, MALIN ISABELLE | ||
13 | ODIFE, IKEMEFUNA | ||
14 | RAM, ROORH | ||
15 | SINGH, AMAR | ||
16 | ABDOLAZIN REZAIE POOR BASHIR | ||
17 | ABDUL MOMIN, ABDUL | ||
18 | ABDUL RAZZAQ, PULIA MANDAN | ||
19 | ABDUL, SALEEM | ||
Data |
This is IniRng
Excel Workbook | |||
---|---|---|---|
A | |||
8 | LUNDBURG, MALIN | ||
9 | ODIFE, IKEMEFUNA | ||
10 | RAM, ROORH | ||
11 | SINGH, AMAR | ||
12 | ABDOLAZIN REZAIE BASHIR | ||
13 | ABDUL MOMIN, ABDUL | ||
14 | ABDUL RAZZAQ, PULIA MANDAN | ||
15 | ABDUL, SALEEM | ||
Historical |
I am trying to compare IniRng with NewRng to see if there are any changes. As can be seen, there are 2 changes (the 1st name and the 5th name)
The code below detects both changes but RemRng is populated with only the 1st change. Can anyone see where I am going wrong? I've tried Union and Application.Union but get same results.<font face=Courier New> <SPAN style="color:#00007F">For</SPAN><SPAN style="color:#00007F">Each</SPAN> rCell<SPAN style="color:#00007F">In</SPAN> IniRng<br> <SPAN style="color:#00007F">If</SPAN> IsError(WorksheetFunction.Match(rCell.Value, NewRng, 0))<SPAN style="color:#00007F">Then</SPAN><br> <SPAN style="color:#00007F">If</SPAN> RemRng<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN><br> <SPAN style="color:#00007F">Set</SPAN> RemRng = rCell<br> <SPAN style="color:#00007F">Else</SPAN><br> <SPAN style="color:#00007F">Set</SPAN> RemRng = Union(RemRng, rCell)<br> <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#00007F">Next</SPAN> rCell<br> RemCount = RemRng.Rows.Count</FONT>