Compare two Ranges VBA

Question

I am trying to compare to ranges in excel with the following VBA code:

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

However I am getting the error Type Mismatch.

Anyone have ideas on what the problem is?

I got this code from another post (Compare Two Ranges) however I can not comment to ask the person who posted the code why I might be getting this problem.

This has been answered: https://stackoverflow.com/questions/22270693/fastest-way-to-check-if-two-ranges-are-equal-in-excel-vba — Jsleshem, Jun 15 '17 at 18:39
I am not getting any errors. with the code. do you have errors in the data? — Scott Craner, Jun 15 '17 at 18:42
The only way I get that error is if one or more of the cells in the test is an error. — Scott Craner, Jun 15 '17 at 18:47
Is this question still open, or was my answer sufficient? If the latter is true, then marking my answer as the correct answer will help other programmers find the answer to this same question if they have it! — Jsleshem, Jul 07 '17 at 20:11

score 1 · Answer 1 · answered Jun 15 '17 at 18:41

1

This has been answered:Fastest way to check if two ranges are equal in excel vba

Note, that the question linked is also marked as a duplicate. Go to that original post to see the full answer. If should help you, and answer everything.

That being said, use this sheet: https://msdn.microsoft.com/en-us/library/office/ff838238.aspx to see everything that the range object can do in VBA.

answered Jun 15 '17 at 18:41

Jsleshem

715
1
10
31

Neither these methods will fix the Type Mismatch problem, albeit the original is more concise. The problem with the type mismatch is that the OP most likely has an error in one or more cells. And we cannot equate errors. – Scott Craner Jun 15 '17 at 19:18

Compare two Ranges VBA

1 Answers1

Linked