testing for range of values

[cocopops2001]

when testing for range of values what is the best way to do it, i want to copy a sheet over to a new book. I want to test five variables and if all five do not match then paste a certain sheet into the new book. can i use something like this.

 If Sheet1.Range("a1", "D5", "n50", "M10", "C6").Value <> Thisworkbook.Sheet(1).Range("a1", "D5", "n50", "M10", "C6").Value Then

''''''copy the sheets

End If

or am i better defining my range as a variable and then testing against that?

 

[Gary McMaster]

I don't know if it's the best way but I would be inclined to concatenate the 5 values from both places and do a single comparison on the concatenated values.

Gary

 

[cocopops2001]

im quite new to programming and i dont understand what that means. can you give me an example line of code please?

 

[Gary McMaster]

Concatenate strings (adds) all the values together. You would only have to do 1 test instead of 5.

You could use the "Concatenate" formula / function on the worksheets to do it for you.

In an unused or hidden cell on both sheets:

=CONCATENATE(A1,D5,N50,M10,C6)

Then you would check to see if both those cells are the same.

Or, basically the same thing in code

Activesheet.Range("A1").value & Activesheet.Range("D5").value & ...

Notice the & operator. Plus (+) can also work but if the values happen to be numeric it will sum the values instead of just stringing all the characters together.

Gary

 

[cocopops2001]

thank you