That's what it seems; the following works in the module for Sheet1:

Sub testme3()
Dim baseRng As Range
Dim rng As Range
Set baseRng = Worksheets("Sheet2").Range("A1")
Set rng = baseRng(2, 2)
MsgBox rng.Parent.Name & " " & rng.Address '<---Displays Sheet2 $B$2
End Sub

Alan Beban

Dave Peterson wrote:
There are even potential problems with this--depending on where the code is:

I put this in the module for Sheet1:

Option Explicit
Sub testme()
Dim baseRng As Range
Dim rng As Range
Set baseRng = Worksheets("sheet2").Range("A1")
Set rng = Range(baseRng(1, 1), baseRng(2, 2))
End Sub

I got an error with that last "set rng = Range(baseRng(1, 1), baseRng(2, 2))"
statement.

If I changed it to:
Set rng = Application.Range(baseRng(1, 1), baseRng(2, 2))

It worked fine.

I bet it's because that unqualified range() is still looking at the sheet that
owns the code.





Alan Beban wrote:

Dave Peterson wrote:

The only trouble I've had with that syntax is when I don't qualify the ranges.

One can avoid the qualification problem with

Set baseRng = ActiveSheet.Range("A1")
Set rng=Range(baseRng(1,1),baseRng(2,2))

You can refer to a single cell with, e.g., baseRng(2,2)

Alan Beban

I like:

dim rng as range
with activesheet 'worksheets("sheet1")
set rng = .range(.cells(1,1),.cells(2,2))
end with

And I could refer to a single cell using your example--but I wouldn't.



pwermuth wrote:


Thank you, but the

Range(Cells(1,1),Cells(2,2)) syntax never works for me. I always get
runtime errors. Are there some limitations I need to be aware of? For
example, can I not refer to a single cell (i.e. Range(Cells(2,2),
Cells(2,2)) ) using this syntax?

--
pwermuth
------------------------------------------------------------------------
pwermuth's Profile: http://www.excelforum.com/member.php...o&userid=24997
View this thread: http://www.excelforum.com/showthread...hreadid=385424