PDA

View Full Version : Select and name a Range



simora
01-15-2010, 02:22 AM
I am trying to select and name a range so that I can compare it to another range.



For Each m In payrange
If m.Value <> "" Then
NewStuff = m.Offset(0, 1).Resize(1, 5)
NewStuff.Select


I thought that I was setting m.Offset(0, 1).Resize(1, 5)
as a Range, but when I go to select it to test it, I'm getting an error.

How do I set m.Offset(0, 1).Resize(1, 5) to a range called NewStuff, and what is the best format for comparing NewStuff to another named range?

If OldStuff <> NewStuff Then MsgBox "NOPE!" is not working.
Thanks

mikerickson
01-15-2010, 02:51 AM
Declaring your variable will help. Set has to be used to assign object variables.

Dim payrange As Range, m As Range
Dim OldStuff As Range
Dim NewStuff As Range

For Each m In payrange
If m.Value <> "" Then
Set NewStuff = m.Offset(0, 1).Resize(1, 5)
NewStuff.Select
End If
Next M

This will return whether two ranges have the same values.
MsgBox Evaluate("SUMPRODUCT(--(" & OldStuff.Address(, , , True) & "=" & NewStuff.Address(, , , True) & "))") = OldStuff.Cells.Count

simora
01-15-2010, 01:45 PM
mikerickson :

Thanks! I was trying to compare the values in the ranges to see if they were the same.