Results 1 to 5 of 5

VBA compare worksheets - Adjustment

Threaded View

  1. #1
    Registered User
    Join Date
    06-06-2012
    Location
    Switzerland
    MS-Off Ver
    MS Excel 365
    Posts
    68

    VBA compare worksheets - Adjustment

    Hi!

    I found the below code in this forum to compare the content of two worksheets within the same workbook. In fact, the code is working pretty good. However, when the code does not find any differences, I have an "out of range". Any idea, how to fix this with a pop-up "Nothing found"? Thanks in advance!

    Option Explicit
    Sub CompareRanges()
    Dim rng(2) As Range
    Dim cDif As Collection
    Dim r&, c&, i%, n&
    Dim val(1), itm, dmp
    
    For i = 0 To 1
    On Error Resume Next
    Set rng(i) = _
    Application.InputBox( _
    "Select a range." & vbLf & _
    "OneCell/AllCells translates to UsedRange", Type:=8)
    If rng(i) Is Nothing Then
    i = i - 1
    ElseIf rng(i).Count = 1 Or rng(i).Count = 2 ^ 24 Then
    Set rng(i) = rng(i).Worksheet.UsedRange
    End If
    Next
    On Error GoTo 0
    If rng(0).Worksheet Is rng(1).Worksheet Then
    If Not Intersect(rng(0), rng(1)) Is Nothing Then
    MsgBox "Ranges overlap"
    Exit Sub
    End If
    End If
    
    Set cDif = New Collection
    val(0) = rng(0).Value
    val(1) = rng(1).Value
    For r = 1 To Application.Min( _
    rng(0).Rows.Count, rng(1).Rows.Count)
    For c = 1 To Application.Min( _
    rng(0).Columns.Count, rng(1).Columns.Count)
    If StrComp(val(0)(r, c), val(1)(r, c), vbTextCompare) <> 0 Then
    cDif.Add Array(r, c)
    End If
    
    Next
    If r Mod 1000 = 1 Then Application.StatusBar = "Comparing row: " & r
    Next
    
    If cDif.Count > Rows.Count Then
    MsgBox "Too many differences!"
    Exit Sub
    End If
    
    Application.StatusBar = "Preparing output"
    ReDim dmp(1 To cDif.Count, 1 To 4)
    For Each itm In cDif
    n = n + 1
    With rng(0)(itm(0), itm(1))
    dmp(n, 1) = .Address
    dmp(n, 2) = .Value
    End With
    With rng(1)(itm(0), itm(1))
    dmp(n, 3) = .Address
    dmp(n, 4) = .Value
    End With
    
    Next
    Application.StatusBar = False
    
    Set rng(2) = Application.InputBox(cDif.Count & _
    "differences found" & vbLf & _
    "Where to dump?", Type:=8)
    rng(2).Resize(cDif.Count, 4) = dmp
    
    End Sub
    Last edited by FixandFoxi; 06-18-2012 at 09:47 AM.

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