Comparing two ranges

[Utkarsh]

Hi
I am comparing two ranges of exactly the same size and prompting the
result as either a complete match or a mismatch even if one value
differs between the ranges. I tried:

Sub comp()
Dim FirstRange As Range, SecondRange As Range

Set FirstRange = Application.InputBox("Set first series: ", Type:=8)
Set SecondRange = Application.InputBox("Set Second series: ", Type:=8)

If Evaluate("SUM((EXACT(FirstRange,SecondRange)=FALSE)*1)") = 0 Then
MsgBox "Ranges are exact"
Else
MsgBox "Ranges differ"
End If
End Sub

This does not work and gives an error on "If Evaluate..." line. Could
someone help please.

I am also wondering if it can accomodate when one or both the values
are #NA type of errors.

Thanks
Utkarsh

 

[RB Smissaert]

Try this code:

Sub comp()

Dim FirstRange As Range
Dim SecondRange As Range
Dim arr1
Dim arr2
Dim i As Long
Dim c As Long

Set FirstRange = Application.InputBox("Set first series: ", Type:=8)
Set SecondRange = Application.InputBox("Set Second series: ", Type:=8)

If FirstRange.Columns.Count <> SecondRange.Columns.Count Or _
FirstRange.Rows.Count <> SecondRange.Rows.Count Then
MsgBox "The 2 ranges are not of the same size and shape!"
Exit Sub
End If

arr1 = FirstRange
arr2 = SecondRange

If FirstRange.Columns.Count = 1 And _
FirstRange.Rows.Count = 1 Then
If arr1 = arr2 Then
MsgBox "Ranges are exact"
Else
MsgBox "Ranges differ"
End If
Exit Sub
End If

For i = 1 To UBound(arr1)
For c = 1 To UBound(arr1, 2)
If Not arr1(i, c) = arr2(i, c) Then
MsgBox "Ranges differ"
Exit Sub
End If
Next
Next

MsgBox "Ranges are exact"

End Sub


RBS

 

[Utkarsh]

Thanks a ton! this works well!