1
    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?

Community
  • 1
  • 1
user2600411
  • 29
  • 2
  • 10

2 Answers2

3

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
Jon Crowell
  • 21,695
  • 14
  • 89
  • 110
  • This is only a small part of a macro I am trying to program so I need to code it as oppose to a few steps task.Thanks for the method though.@HeadofCatering – user2600411 Jul 25 '13 at 21:08
  • I've edited my answer to include a VBA version. Let me know if that works for you. – Jon Crowell Jul 25 '13 at 21:13
  • I am using rows from two different worksheet each with more than one row. I am trying to figure out a loop in which the comparison line should reside. The rows may not be on the same row level but I only need to compare cells on A to C column for each comparison. @HeadofCatering – user2600411 Jul 25 '13 at 21:24
  • I edited the answer to allow you to pass in ranges from 2 different sheets. The ranges can have multiple rows but must be of the same dimensions. – Jon Crowell Jul 25 '13 at 21:41
0

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

ApplePie
  • 8,814
  • 5
  • 39
  • 60