vba - 比较具有相同单元格数的两个行范围

Question

    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

假设 A14:C14(1,2,3) 和 A15:C15(1,2,3) 具有相同的值，我收到类型不匹配错误。
我想如何比较这样的两个范围？

Answer 1

你不需要VBA。试试这个数组公式:

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

输入公式后，使用 Ctrl+Shift+Enter 将其设置为数组公式。

如果范围相等，它将返回 TRUE，否则返回 FALSE。

如果您需要使用 VBA 执行此操作，请尝试以下操作:

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

编辑:
我不喜欢 ActiveSheet，也不喜欢硬编码的范围，所以这里有一个更完整的片段:

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