Excel Help Forum - View Single Post

#6 07-08-2005, 06:05 PM

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
> >
> >

--

Dave Peterson