VBA compare named ranges to see if they are identical

[jim101]

I have 2 named ranges Test1 and Test2,
Test1 is A1:I9 and Test2 is K11:S19,
The ranges will have number and blank cells in them, how can I compare them in VBA to let me know if they are identical.
Thanks

Some thing like this, but this does not work, for excel 2003

If Test1 = Test2 Then
MsgBox "Named Ranges Are The Same"
End If
End Sub

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG19Aug46
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Range("Test1")
    [COLOR="Navy"]If[/COLOR] Not Dn = Range("Test2")(Dn.row, Dn.Column) [COLOR="Navy"]Then[/COLOR] MsgBox "Disimilar ranges": [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]Next[/COLOR] Dn
MsgBox "Both Ranges have the same data"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[patel45]

to compare ranges you have to compare single cells, you can try with this code, but it's not reliable

Sub rangecompare()
  Const sRng1 As String = "A1:C4" 
  Const sRng2 As String = "E1:G4"
  Dim vRis As Variant
  vRis = Evaluate("=AND((A2:C4)=(E2:G4))")
  
  If Not IsError(vRis) Then
    If vRis Then MsgBox "identical" Else MsgBox "identical"
  Else
    MsgBox "error"
  End If
 
End Sub

Also matrix formulas like ={E((A2:C4)=(E2:G4))} are not reliable

 

[patel45]

........
If Not Dn = Range("Test2")(Dn.row, Dn.Column)
.........

Hi MickG
very good code, can you explain me ?

 

[jim101]

Mick, that works, how about if I wanted to check Test1 to see if it matches more that 1 other range, say I wanted to test it to see if if it matches Test2 or Test3 can this be done also.
Thanks

 

[MickG]

This code below may look more familar:-
All you doing is comparing the same row & column of range "Test1" with the same Row & column Of range "Test2"
When you precede the "Cells(dn.row,dn.column)" with "Range("Test"), you're relating to the range "Test2" and your first cell in both ranges will be Cells(1,1), doesn't matter where the ranges are !!!!

Range("Test2").Cells(Dn.row, Dn.Column)

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG19Aug11
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Fd [COLOR="Navy"]As[/COLOR] Boolean
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] Byte
[COLOR="Navy"]For[/COLOR] n = 2 To 3
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Range("Test1")
    Fd = Dn <> Range("Test" & n).Cells(Dn.row, Dn.Column)
    [COLOR="Navy"]If[/COLOR] Fd = True [COLOR="Navy"]Then[/COLOR] [COLOR="Navy"]Exit[/COLOR] For
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]If[/COLOR] Fd = True [COLOR="Navy"]Then[/COLOR]
 MsgBox "Ranges ""Test1"" and " & """Test" & n & """ [COLOR="Navy"]Do[/COLOR] Not Match"
[COLOR="Navy"]Else[/COLOR]
 MsgBox "Ranges ""Test1"" and " & """Test" & n & """ Match"
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[patel45]

This code below may look more familar:-
Range("Test2").Cells(Dn.row, Dn.Column)

Ok, thanks !

 

[jim101]

Mick, that will do it, was playing around with the first code you sent

Sub MG19Aug46
Dim Dn As Range
For Each Dn In Range("Test1")
    If Not Dn = Range("Test2")(Dn.row, Dn.Column) Then MsgBox "Disimilar ranges": Exit Sub
Next Dn
MsgBox "Both Ranges have the same data"
End Sub

If I make some more ranges say Test3 and Test4 and change the code to this it will not work???, even if Test3 and Test4 are the same it comes back with disimilar ranges
Why would that be? Thanks

Sub MG19Aug46
Dim Dn As Range
For Each Dn In Range("Test3")
    If Not Dn = Range("Test4")(Dn.row, Dn.Column) Then MsgBox "Disimilar ranges": Exit Sub
Next Dn
MsgBox "Both Ranges have the same data"
End Sub

 

[MickG]

It worked in the first code , because, like my ranges I imagine you first range started "A1" this then returned a frtrow of 1, and a first column of 1.
If you change the positions of the ranges you have to account for it in the code, so that each range and column start at Row 1 and column 1. I think the code below should achieve that.

[COLOR="Navy"]Sub[/COLOR] MG20Aug17
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Cl [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Range("Test3")
   Rw = Range("Test3")(1).row
    Cl = Range("Test3")(, 1).Column
     [COLOR="Navy"]If[/COLOR] Not Dn = Range("Test4")(Dn.row - Rw + 1, Dn.Column - Cl + 1) [COLOR="Navy"]Then[/COLOR] MsgBox "Disimilar ranges": [COLOR="Navy"]Exit[/COLOR] [COLOR="Navy"]Sub[/COLOR]
[COLOR="Navy"]Next[/COLOR] Dn
MsgBox "Both Ranges have the same data"
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick