Got it. I figured it might be a typo, but I wasn't sure if you had some
other points in mind.
--
Thanks for the feedback,
Jay

"Dave Peterson" wrote:

If you're writing about this line:

set i = ws.Range(ws3.Cells(2, x), ws3.Cells(lRow, x))
then you're right. But it was more of a typo (copy|paste) error than anything.

set i = ws.Range(ws.Cells(2, x), ws.Cells(lRow, x))

(I copied from a combination of previous posts and should have just typed the
line in the message.)

But I'll stand by the one I actually typed!

dim ws as worksheet
dim i as range
set ws = worksheets("someothersheet")
with ws
set i = .Range(.Cells(2, x), .Cells(lRow, x))
end with






Jay wrote:

Hi Dave - could you check your syntax ? I might be misunderstanding your
post, but both statements you posted produce an error (winXP/XL03) regardless
of the location (general module or worksheet module).

My thought is that this type of statement can never ask for the range object
from one worksheet based on the cells object from another; they're
incompatibility produces the error.

Let us know what you think...

--
Respectfully,
Jay

"Dave Peterson" wrote:

It also depends on where the code is.

An unqualified range (without the ws. stuff) in a general module, will refer to
the activesheet.

An unqualified range in a worksheet module will refer to the sheet that owns the
code.

dim i as range
set ws = worksheets("someothersheet")
'this will always work
set i = ws.Range(ws3.Cells(2, x), ws3.Cells(lRow, x))

'this will cause an error if you're in a worksheet module (not someothersheet,
though):
set i = ws.Range(Cells(2, x), Cells(lRow, x))


=====
To save typing (and easier to understand (for me at least)), I'd use:

dim ws as worksheet
dim i as range
set ws = worksheets("someothersheet")
with ws
set i = .Range(.Cells(2, x), .Cells(lRow, x))
end with

Note the dot's in front of .range and both .cells.






Gary Keramidas wrote:

just wondering if these are functionally the same:

i = Range(ws3.Cells(2, x), ws3.Cells(lRow, x))
i = ws3.Range(Cells(2, x), Cells(lRow, x))

--

Gary

--

Dave Peterson


--

Dave Peterson