Compare lists, and paste variance in new sheet

**BTYOUNG** · 09-01-2011, 09:13 PM

Hello, I am trying to compare lists from two different sheets and then paste the variance on a new sheet, but it only seems to paste the last variance found.

Here are the specs:
Sheet 1 is called SerialNumSubReport
Sheet 2 is called PHYSICAL_VERIFICATION
Sheet 3 is called RESULTS

Sheet 1 contains a report output that includes serial numbers in Column G, Sheet two contains serial numbers physically found in column A (these will be entered by the user), Sheet 3 will contain a list of the Serial number(s) that are on sheet 1 but can not be found on Sheet 2.

Right now my code is as follows, but it only returns the last variance, not a list of the variances. I'm sure I'm messing up something in the loop, but I'm very new to VBA so I can't quite figure it out.


Sub VerifyMatches2()

Dim PartRngSheet1 As Range, PartRngSheet2 As Range, Short

Dim lastrowsheet1 As Long, lastrowsheet2 As Long, lastrowshort As Long

Dim cl As Range, rng As Range, sht As Range


lastrowsheet1 = Worksheets("SerialNumSubReport").Range("G65536").End(xlUp).Row
Set PartRngSheet1 = Worksheets("SerialNumSubReport").Range("G1:G" & lastrowsheet1)

lastrowsheet2 = Worksheets("PHYSICAL_VERIFICATION").Range("A65536").End(xlUp).Row
Set PartRngSheet2 = Worksheets("PHYSICAL_VERIFICATION").Range("A1:A" & lastrowsheet2)

lastrowshort = Worksheets("RESULTS").Range("A65536").End(xlUp).Row
Set Short = Worksheets("RESULTS").Range("A1:A" & lastrowshort)



For Each cl In PartRngSheet1
    For Each rng In PartRngSheet2
        For Each sht In Short
          If (cl <> rng) Then
            sht = cl
            End If
        Next sht
    Next rng
Next cl

                
End Sub

Any Thoughts? I also attached a copy of the macro document, with sample data included.

**Trebor76** · 09-01-2011, 10:11 PM

Hi BTYOUNG,

Welcome to the forum!!

While I haven't coloured the unmatched entries in red, try this:

Option Explicit
Sub Macro1()

    'http://www.excelforum.com/excel-programming/790653-compare-lists-and-paste-variance-in-new-sheet.html

    Dim rngCell As Range, _
        rngFoundCell As Range
        
    Application.ScreenUpdating = False
    
    For Each rngCell In Sheets("SerialNumSubReport").Range("G1:G" & Sheets("SerialNumSubReport").Cells(Rows.Count, "G").End(xlUp).Row)
    
        Set rngFoundCell = Sheets("PHYSICAL_VERIFICATION").Range("A1:A" & Sheets("SerialNumSubReport").Cells(Rows.Count, "A").End(xlUp).Row).Find(What:=rngCell.Value, _
                                                            LookIn:=xlValues, _
                                                            LookAt:=xlWhole, _
                                                            SearchOrder:=xlByRows, _
                                                            SearchDirection:=xlNext, _
                                                            MatchCase:=True, SearchFormat:=False)
                                                   
        'If the 'rngFoundCell' variable has not been set (is nothing), then...
        If rngFoundCell Is Nothing Then
            '...copy the cell value to the next available row in column A of the 'RESULTS' tab.
            Sheets("RESULTS").Range("A" & Sheets("RESULTS").Cells(Rows.Count, "A").End(xlUp).Row + 1).Value = rngCell.Value
        End If
        
    Next rngCell
    
    Application.ScreenUpdating = True
    
End Sub

HTH

Robert

Thread: Compare lists, and paste variance in new sheet

Thread Tools

Display

Compare lists, and paste variance in new sheet

Re: Compare lists, and paste variance in new sheet

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions