+ Reply to Thread
Results 1 to 3 of 3

macro compare 2 ranges but insert row in one range..

Hybrid View

  1. #1
    Registered User
    Join Date
    06-06-2012
    Location
    Mexico
    MS-Off Ver
    Excel 2010
    Posts
    99

    Question macro compare 2 ranges but insert row in one range..

    Hi excel forum user's!

    I need Help, I have this macro:

    Sub CompareWorksheetRanges(rng1 As Range, rng2 As Range)
    Dim r As Long, c As Integer
    Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
    Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
    Dim rptWB As Workbook, DiffCount As Long
        If rng1 Is Nothing Or rng2 Is Nothing Then Exit Sub
        If rng1.Areas.Count > 1 Or rng2.Areas.Count > 1 Then
            MsgBox "Can't compare multiple selections!", _
                vbExclamation, "Compare Worksheet Ranges"
            Exit Sub
        End If
        Application.ScreenUpdating = False
        Application.StatusBar = "Creating the report..."
        Set rptWB = Workbooks.Add
        Application.DisplayAlerts = False
        While Worksheets.Count > 1
            Worksheets(2).Delete
        Wend
        Application.DisplayAlerts = True
        With rng1
            lr1 = .Rows.Count
            lc1 = .Columns.Count
        End With
        With rng2
            lr2 = .Rows.Count
            lc2 = .Columns.Count
        End With
        maxR = lr1
        maxC = lc1
        If maxR < lr2 Then maxR = lr2
        If maxC < lc2 Then maxC = lc2
        If lr1 <> lr2 Or lc1 <> lc2 Then
            If MsgBox("The two ranges you want to compare are of different size!" & _
                Chr(13) & "Do you want to continue anyway?", _
                vbQuestion + vbYesNo, "Compare Worksheet Ranges") = vbNo Then Exit Sub
        End If
        DiffCount = 0
        For c = 1 To maxC
            Application.StatusBar = "Comparing cells " & _
                Format(c / maxC, "0 %") & "..."
            For r = 1 To maxR
                cf1 = ""
                cf2 = ""
                On Error Resume Next
                cf1 = rng1.Cells(r, c).FormulaLocal
                cf2 = rng2.Cells(r, c).FormulaLocal
                On Error GoTo 0
                If cf1 <> cf2 Then
                    DiffCount = DiffCount + 1
                    Cells(r, c).Formula = cf1 & cf2
                End If
            Next r
        Next c
        Application.StatusBar = "Formatting the report..."
        With Range(Cells(1, 1), Cells(maxR, maxC))
            .Interior.ColorIndex = 19
            With .Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlHairline
            End With
            With .Borders(xlEdgeRight)
                .LineStyle = xlContinuous
                .Weight = xlHairline
            End With
            With .Borders(xlEdgeLeft)
                .LineStyle = xlContinuous
                .Weight = xlHairline
            End With
            With .Borders(xlEdgeBottom)
                .LineStyle = xlContinuous
                .Weight = xlHairline
            End With
            On Error Resume Next
            With .Borders(xlInsideHorizontal)
                .LineStyle = xlContinuous
                .Weight = xlHairline
            End With
            With .Borders(xlInsideVertical)
                .LineStyle = xlContinuous
                .Weight = xlHairline
            End With
            On Error GoTo 0
        End With
        Columns("A:IV").ColumnWidth = 20
        rptWB.Saved = True
        If DiffCount = 0 Then
            rptWB.Close False
        End If
        Set rptWB = Nothing
        Application.StatusBar = False
        Application.ScreenUpdating = True
        MsgBox DiffCount & " cells contain different formulas!", _
            vbInformation, "Compare Worksheet Ranges"
    End Sub
    
    
    Sub TestCompareWorksheetRanges()
        ' comparacion entre los mismos rangos
        CompareWorksheetRanges Range("A3:C" & Cells(Rows.Count, 2).End(xlUp).Row), Range("H3:J" & Cells(Rows.Count, 2).End(xlUp).Row)
    
    End Sub
    I have 2 ranges to compare:
    Range 1: ("A3:C")
    Range 2: ("H3:J")

    I need you when it detects a difference in those ranges, insert rows (only RANGE (H3:M) until it detects the same value and continue ..
    No problem if create a new workbook or can be in the same worksheet..

    attach the worksheet and include explication..

    Thanks a lot!
    Best Regards!!
    Attached Files Attached Files
    Last edited by feroguz; 12-27-2012 at 01:25 PM.

  2. #2
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: macro compare 2 ranges but insert row in one range..

    Hi, Roberto
    try it
    Sub CompareWorksheetRangesERTERT()
    Dim x, y, u(), i&, j&, k&, s$, ubx&
    x = Range("A1").CurrentRegion.Value: ubx = UBound(x)
    y = Range("H1").CurrentRegion.Value
    ReDim u(1 To ubx + UBound(y), 1 To UBound(y, 2))
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For i = 1 To ubx
            .Item(Join(Array(x(i, 1), x(i, 2), x(i, 3)), "~")) = i
        Next i
    
        For i = 1 To UBound(y)
            s = Join(Array(y(i, 1), y(i, 2), y(i, 3)), "~")
            If .Exists(s) Then
                k = .Item(s)
                For j = 1 To UBound(u, 2): u(k, j) = y(i, j): Next j
            Else
                ubx = ubx + 1
                For j = 1 To UBound(u, 2): u(ubx, j) = y(i, j): Next j
            End If
        Next i
    End With
    With Range("H1:M1")
        .CurrentRegion.ClearContents: .Resize(ubx).Value = u()
    End With
    End Sub
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    06-06-2012
    Location
    Mexico
    MS-Off Ver
    Excel 2010
    Posts
    99

    Re: macro compare 2 ranges but insert row in one range..

    as always .. Nilem the best!
    Thread closed.!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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