Compare two sheets and perform action routine
I'm trying to compare two sheets with a routine. The intention is that if it finds a duplicate then it will place a comment on another column... I am not sure what I'm doing wrong but it does not seem to be doing anything. BTW this is a routine from the microsoft website that I modified slightly to meet my needs. Here's the routine... 'Compare Query Report vs State Reject Report Dim iListCount As Integer Dim iCtr As Integer Dim lastrowquery As Integer Dim lastrowreport As Integer lastrowquery = Sheets("Query Report").Range("A1").End(xlDown).Row lastrowreport = Sheets("Sheet2").Range("A6").End(xlDown).Row ' Get count of records to search through (list that will have tags). iListCount = Sheets("Sheet2").Range("C1:C" & lastrowreport).Rows.Count ' Loop through the Query Report list. For Each x In Sheets("Query Report").Range("C1:C" & lastrowquery) ' Loop through all records in the second list. For iCtr = 1 To iListCount ' Do comparison of next record. ' To specify a different column, change 1 to the column number. If x.Value = Sheets("Sheet2").Cells(iCtr, 9).Value Then ' If match is true then tag with No Change. Sheets("Sheet2").Cells(iCtr, 9).FormulaR1C1 = "No Change" ' Increment counter to account for deleted row. iCtr = iCtr + 1 End If Next iCtr Next Any help would be appreciated... -- wayliff ------------------------------------------------------------------------ wayliff's Profile: http://www.excelforum.com/member.php...o&userid=29860 View this thread: http://www.excelforum.com/showthread...hreadid=501783 |
Compare two sheets and perform action routine
Hi,
The statement iCtr = iCtr + 1 appears to be redundant as no rows are being deleted but unless we know what you are comparing i.e what columns (C with I ?) it is difficult to say what is wrong. Duplicates would give the message "No Change" in Sheet2 Column I. "wayliff" wrote: I'm trying to compare two sheets with a routine. The intention is that if it finds a duplicate then it will place a comment on another column... I am not sure what I'm doing wrong but it does not seem to be doing anything. BTW this is a routine from the microsoft website that I modified slightly to meet my needs. Here's the routine... 'Compare Query Report vs State Reject Report Dim iListCount As Integer Dim iCtr As Integer Dim lastrowquery As Integer Dim lastrowreport As Integer lastrowquery = Sheets("Query Report").Range("A1").End(xlDown).Row lastrowreport = Sheets("Sheet2").Range("A6").End(xlDown).Row ' Get count of records to search through (list that will have tags). iListCount = Sheets("Sheet2").Range("C1:C" & lastrowreport).Rows.Count ' Loop through the Query Report list. For Each x In Sheets("Query Report").Range("C1:C" & lastrowquery) ' Loop through all records in the second list. For iCtr = 1 To iListCount ' Do comparison of next record. ' To specify a different column, change 1 to the column number. If x.Value = Sheets("Sheet2").Cells(iCtr, 9).Value Then ' If match is true then tag with No Change. Sheets("Sheet2").Cells(iCtr, 9).FormulaR1C1 = "No Change" ' Increment counter to account for deleted row. iCtr = iCtr + 1 End If Next iCtr Next Any help would be appreciated... -- wayliff ------------------------------------------------------------------------ wayliff's Profile: http://www.excelforum.com/member.php...o&userid=29860 View this thread: http://www.excelforum.com/showthread...hreadid=501783 |
Compare two sheets and perform action routine
The macro is intended to compare Column C of two different sheets. For the exercise let's call sheet1 and sheet2. Compare col c sheet 1 vs col c sheet 2. If duplicates then on sheet col i write "text". -- wayliff ------------------------------------------------------------------------ wayliff's Profile: http://www.excelforum.com/member.php...o&userid=29860 View this thread: http://www.excelforum.com/showthread...hreadid=501783 |
All times are GMT +1. The time now is 10:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com