CODEWITHSUNDEEP
vbaexcel
user2600411
user2600411

Reputation: 71

compare two row ranges with same number of cells

    Sub compareRange()
        If Worksheets("Sheet1").Range("A14:C14") = Worksheets("Sheet1").Range("A15:C15") Then
            MsgBox "Two Ranges are the same"
        End If
        'MsgBox "Two Ranges are the same"
    End Sub

Giving that A14:C14(1,2,3) and A15:C15(1,2,3) has same values, i am getting a type mismatch error. How am i suppose to compare two ranges like this?

Upvotes: 1

Views: 3706

Answers (2)

Jon Crowell
Jon Crowell

Reputation: 22338

You don't need VBA for that. Try this array formula:

=AND(EXACT(A14:C14,A15:C15))

After typing in the formula, use Ctrl+Shift+Enter to set it as an array formula.

It will return TRUE if the ranges are equal and FALSE if they aren't.

If you need to do this using VBA, try this:

Sub CompareTwoRanges()
    Dim compareRanges As Boolean
    compareRanges = ActiveSheet.Evaluate("=AND(EXACT(A14:C14,A15:C15))")
End Sub

EDIT: I don't like ActiveSheet, and I don't like the hard-coded ranges, so here is a more complete snippet:

Sub CompareTwoRanges()
    Dim wb As Workbook
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim rng1 As Range
    Dim rng2 As Range

    Set wb = ThisWorkbook
    Set ws1 = wb.Sheets("Sheet1")
    Set ws2 = wb.Sheets("Sheet2")
    Set rng1 = ws1.Range("A14:C14")
    Set rng2 = ws2.Range("N3:P3")

    If rangesAreEqual(rng1, rng2, ws1, ws2) Then
        MsgBox "The ranges are equal."
    Else
        MsgBox "Sorry. The ranges are NOT equal."
    End If
End Sub

Function rangesAreEqual(rng1 As Range, rng2 As Range, _
    ws1 As Worksheet, ws2 As Worksheet) As Boolean
    ' booleans default to false
    ' verify ranges have same dimensions
    If rng1.Columns.Count <> rng2.Columns.Count Then Exit Function
    If rng1.Rows.Count <> rng2.Rows.Count Then Exit Function

    ' ranges are the same size. are their contents equal?
    rangesAreEqual = ws1.Evaluate("=AND(EXACT(" & ws1.Name & "!" & _
            rng1.Address & "," & ws2.Name & "!" & rng2.Address & "))")
End Function

Upvotes: 3

ApplePie
ApplePie

Reputation: 8942

You just can't do this. This is not how ranges work. You need to iterate through each cell in the ranges using some kind of for loop. You may want to add checks so that you spot cases where the number of cells is not equal (obviously the ranges are different if that is the case).

Upvotes: 0

Related Questions