Results 1 to 3 of 3

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

Threaded 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.

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