Results 1 to 19 of 19

Compare cell by cell in sheets

Threaded View

  1. #16
    Valued Forum Contributor gjlindn's Avatar
    Join Date
    08-01-2011
    Location
    Dodgeville, WI
    MS-Off Ver
    Excel 2003, 2007, 2010, 2013
    Posts
    369

    Re: Compare cell by cell in sheets

    Here you go!
    Option Explicit
    Option Compare Text
    
    Sub CompareValues()
        Dim wsCopies    As Worksheet
        Dim rFirst      As Range
        Dim rSecond     As Range
        Dim rCell       As Range
        Dim rMatch      As Range
        Dim lCount      As Long
        Dim bScrUpd     As Boolean
        Dim bEvents     As Boolean
        Dim lCalc       As Long
        
        'Improve performance
        With Application
            bScrUpd = .ScreenUpdating
            bEvents = .EnableEvents
            lCalc = .Calculation
            .ScreenUpdating = False
            .EnableEvents = False
            .Calculation = xlCalculationManual
        End With
        
        'Change A1:A19 to your range with duplicate values
        Set rFirst = Sheets("src").Range("A1:A19")
        
        'Change A1:A6 to your range with no duplicate values
        Set rSecond = Sheets("Mast").Range("A1:A6")
        
        'Set worksheet Copies = worksheet object wsCopies
        On Error Resume Next
            Set wsCopies = Sheets("Copies")
            wsCopies.Cells.Clear
            If Err.Number <> 0 Then
                Set wsCopies = Worksheets.Add(After:=Sheets(Sheets.Count))
                wsCopies.Name = "Copies"
            End If
        On Error GoTo 0
        
        'Find rSecond in rFirst and add to Copies worksheet
        For Each rCell In rSecond
            Set rMatch = rFirst.Find(rSecond, LookIn:=xlValues, LookAt:=xlWhole)
            If Not rMatch Is Nothing Then
                Set rCell = rCell.Resize(1, 2)
                wsCopies.Cells(lCount + 1, 1).Resize(1, _
                    rCell.Columns.Count).Value = rCell.Value
                lCount = lCount + 1
            End If
        Next
        
        'Clean up
        Set rFirst = Nothing
        Set rSecond = Nothing
        Set rMatch = Nothing
        Set rCell = Nothing
        
        'Restore Application settings
        With Application
            .ScreenUpdating = bScrUpd
            .EnableEvents = bEvents
            .Calculation = lCalc
        End With
    End Sub
    Good luck!
    Last edited by gjlindn; 09-02-2011 at 01:29 AM. Reason: Corrected boolean to Long

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1