Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Compare sheet1 and sheet2 and have the results on sheet3

0

I am trying to compare two sheets with the same information on both. On sheet1 = my inventory on sheet two is my data that I want to compare with sheet1 and have the differences on sheet3.

I have tried this Macro....

Sub COMPARESHEET()
Dim erowsht1, erowsht2, erow As Long
Dim ecolsht1, ecolsht2, ecol As Long
Dim row, col As Integer
row = 2
col = 1
'find the lastrow on both sheets
erowsht1 = Sheet1.Cells(1, 1).CurrentRegion.Rows.Count
erowsht2 = Sheet2.Cells(1, 1).CurrentRegion.Rows.Count
'which is the greater of the two that will be the end row
If erowsht2 >= erowsht1 Then
    erow = erowsht2
Else
    erow = erowsht1
End If
'find the last column in both sheets
'which ever is the larges colunn number will be the end column
ecolsht1 = Sheet1.Cells(1, 1).CurrentRegion.Columns.Count
ecolsht2 = Sheet2.Cells(1, 1).CurrentRegion.Columns.Count
If ecolsht2 >= ecolsht1 Then
    ecol = erowsht2
Else
    ecol = ecolsht1
End If
'start with a1 and go to last row and last column in both sheets
Do Until row = erow + 1
  For col = 1 To ecol + 1
  If Worksheets("Sheet1").Cells(row, col) <> Worksheets("Sheet2").Cells(row, col) Then
        Worksheets("Sheet2").Cells(row, col).Interior.ColorIndex = 5
         'Worksheets("Sheet3").Cells(row, col).Value = Worksheets("Sheet2").Cells(row, col)
        End If
         Next col
     row = row + 1
    Loop
End Sub
excelvlookupmacros-vba
MS Office Version: 365
posted by: NIXONPHOTOS (rep: 4) on Fri Oct 30, 2020 at 10:52 am
Answer
Discuss

Discussion

"It doesn't seem to work" is short of a full description of your test result. Don't put that result in the Discussion. Instead, edit your question to include it. And while editing, please also select all your code and cick the CODE button at the top. That will add CODE tags to your code which will help anyone copy it with a single click. I think, saving the time and effort of those whom you are asking for help is a matter of common courtesy.
Variatus (rep: 4889) Oct 30, '20 at 7:58 pm
Add to Discussion

Answers

0
Selected Answer

You expanded a lot of energy on preparing for action. That is the best part of your code. Definitely, that's the way to go about it!  Unfortunately there are flaws.

  1. A line of code can contain declarations of different data types, meaning not all declarations in one line are of the same data type.Therefore Dim row, Dim Col As Long makes row a Variant and only col a Long. In the case of this code there is unlikely to be any adverse effect.
  2. However, there is a logical error, too. Your code expressly states that you expect the two sheets to be of different length. Once any row in the two sheets is different all following must also be different. Therefore there is no point in comparing row 1 with row 1, row 2 with row 2 etc. Your data don't fit this picture.
  3. Your code examines each cell in each row and marks the different ones.That's strange. One difference should be sufficient to know that the row is different. One might even know in which cell the difference would occur and check only that one.

In most cases careful preparation leads to the discovery of logical flaws. My guess is that you stopped examining your concept because you changed it. Instead of marking differences you decided to write them to another sheet. There is no evidence in your code or elsewhere in your question what to write to another sheet. Your logic has a huge white hole in that regard. Actually, you would have done yourserlf a favour by appending sample data to your question but, unfortunately, you decided to abandon the advice of your better nature to conserve your time and effort. That isn't the way to success in programming.

  • The basic system you need is to take one list and compare it to the other. Taking the longer (or shorter) list as a base - in other words, not knowing which one is the base - complicates things unnecessarily. 
  • Each item in the base list must be searched for in the entire other list. Concentrate on excluding mismatches rather than finding them. Meaning, you look for a match and if there is no match then it's a mismatch.
    • If you can look for a match in a single cell to identify a match that's comparatively easy. You can use VBA's Find function or WorksheetFunction.Match().
    • If you need to examine several or even all cells the task becomes quite burdensome, and time consuming, too. But you still start with one.
  • Al matched items on the other sheet must be marked. Have your code add a column that you later remove. I would try to create an array to store the row numbers of found matches.
  • In the end, all unmatched rows are "different". Most likely, it will be more efficient to deal with them after the comparison is done.
posted by: Variatus (rep: 4889) on Sun Nov 1, 2020 at 8:02 pm
Discuss

Discussion

I went ahead and attached the excel file that I am using to learn...i want to compare sheet1 with sheet2 and have any differences appear on sheet3.

(i.e. S1-12, S13-22, S100-107 have been scanned which is on sheet1 - sheet2 I am using as my data to compare sheet1 too. I am looking for something to look at both sheet1 and sheet2 and show on sheet3 S23-99 and then S108-198 were not scanned.)
NIXONPHOTOS (rep: 4) Nov 2, '20 at 7:50 am
You asked a question and received an answer. If the answer was helpful please select it. Helpful or not helpful are the only two options here because this is a Q & A forum, not a discussion blog.
Variatus (rep: 4889) Nov 2, '20 at 7:04 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login