When to use Offset versus Cells

[John Kauffman]

It seems Range().Offset() and Range().Cells() can acheive the same thing: a new range based on an original range.

I see both used in samples but can not figure out why one is selected over the other in each case.

Can anyone explain logic of which property to use in which cases?

Thanks.

 

[meldoc]

Hello

This should help.

http://msdn.microsoft.com/en-us/library/aa139976(office.10).aspx

 

[jbeaucaire]

In general, I use OFFSET() when I want to refer to a new range OUTSIDE of an already known/defined range, even if the range is a single cell.


I use the CELLS() method to define/select a specific subrange INSIDE of an already known/defined range.

 

[John Kauffman]

Jerry:
Sounds like a good rule of thumb (and easy to understand).
Is there a logic behind that or is it a convention for which it is easy to understand when maintaining code?
Thanks.

(Still reading the link supplied by Brian / MELDOC)

 

[jbeaucaire]

What do you mean by "is there a logic"? My brief response was to outline my brief logic. Is there something else specific you're trying to figure out?

 

[John Kauffman]

Sorry if I irritated. I appreciate your help.

I was thinking of logic as something like
Offset() does not allow xxx, so best for outside a range
On the other hand, Cells() does not allow yyy, so best for inside a range

Maybe I am all wrong, but it seems both Offset() and Cell() are able to do the job of referencing another range relative to current range. Maybe there is not that functional overlap so my question is illogical.

My best guess is something along the lines that variable types that can be accepted by the Cell() or Offset() args are different and thus more useful inside/outside range.

Again, I appreciate your help.

 

[shg]

All of these refer to the same cell:

Range("myRange").Cells(1,2)
Range("myRange").Offset(0,1)
Range("myRange").Range("B1")

If I were using computed subscripts, I'd prefer one of the first two -- and if possible, the one that didn't require any additional arithmetic with the subscripts.

 

[John Kauffman]

Meldoc:

I read your ref page. I had found that, but read again on your recommendation. I got it - two differences. Encapsulated here for future searches.

Argument count base:
Offset(a,b) is 0
Cells(a,b) is 1

Resulting range if outer range is multicell:
Range(multicell).Offset() result is range of same shape as outer range
Range(multicell).Cells() always refers to a single cell