syntax question
 

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

syntax question
 

Gary,
If the ActiveSheet is ws3 both will work.
If the ActiveSheet is some other sheet both will blow up.
I guess in that regard they both function the same. <g

The preferred method is to prefix Range and Cells with the
worksheet...
ws3.Range(ws3.Cells(2, x), ws3.Cells(lRow, x))
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Gary Keramidas" <GKeramidasATmsn.com
wrote in message
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

syntax question
 

ok, thanks jim, i thought they would do the same thing, but wasn't sure.
i knew they would only work if the sheet was active.
thanks for the reply.
--


Gary


"Jim Cone" wrote in message
...

Gary,
If the ActiveSheet is ws3 both will work.
If the ActiveSheet is some other sheet both will blow up.
I guess in that regard they both function the same. <g

The preferred method is to prefix Range and Cells with the
worksheet...
ws3.Range(ws3.Cells(2, x), ws3.Cells(lRow, x))
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Gary Keramidas" <GKeramidasATmsn.com
wrote in message
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

syntax question
 

jim:

why does this work
Application.Sum(Range(ws4.Cells(2, x), ws4.Cells(lRow, x)))
and this doesn't
Application.Sum(ws4.Range(Cells(2, x),Cells(lRow, x)))

--


Gary


"Jim Cone" wrote in message
...

Gary,
If the ActiveSheet is ws3 both will work.
If the ActiveSheet is some other sheet both will blow up.
I guess in that regard they both function the same. <g

The preferred method is to prefix Range and Cells with the
worksheet...
ws3.Range(ws3.Cells(2, x), ws3.Cells(lRow, x))
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Gary Keramidas" <GKeramidasATmsn.com
wrote in message
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

syntax question
 

i guess to be more exact:
this works
If Application.Sum(Range(ws4.Cells(2, x), ws4.Cells(lRow, x))) 0 Then

this doesn't

If Application.Sum(ws4.Range(Cells(2, x), Cells(lRow, x))) 0 Then

--


Gary


"Jim Cone" wrote in message
...

Gary,
If the ActiveSheet is ws3 both will work.
If the ActiveSheet is some other sheet both will blow up.
I guess in that regard they both function the same. <g

The preferred method is to prefix Range and Cells with the
worksheet...
ws3.Range(ws3.Cells(2, x), ws3.Cells(lRow, x))
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Gary Keramidas" <GKeramidasATmsn.com
wrote in message
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

syntax question
 

I agree with Jim, Gary. They are functionally the same when ws3 is the
activesheet.

And, I guess you could technically consider them fully equivalent under that
condition. Here's why: If you check the online help for the range property
and the cells property, you'll find the statements (paraphrased here):

Range Property: When used without an object qualifier, this property is a
shortcut for the phrase €œActiveSheet.Range€� (it returns a range from the
active sheet), and

Cells Property: Using this property without an object qualifier returns a
Range object that represents all the cells on the active worksheet.
Essentially the Cells property is a shortcut for the phrase
€œActiveSheet.Cells.€�

Therefore, when ws3 is the activesheet, substitute "activesheet" for ws3 in
both of your statements and convert the shorthand versions of Range and Cells
to their longhand version (by prefixing them with 'activesheet.'):

i = Range(ws3.Cells(2, x), ws3.Cells(lRow, x)) is equivalent to
i = activesheet.range(activesheet.cells(2, x), activesheet.Cells(lRow, x))

And,

i = ws3.Range(Cells(2, x), Cells(lRow, x)) is also equivalent to
i = activesheet.range(activesheet.cells(2, x), activesheet.cells(lRow, x))

You can see here that your original statements are equivalent in every way
when ws3 is the active sheet.

Now, when ws3 is not the active sheet, they are no longer equivalent,
functionally or otherwise. Here's why:

Statement 1 does not produce a runtime error and in fact sets a valid object
in variable €˜i while Statement 2 simply fails, which makes them different.
The arguments of the range property in Statement 1 are simply explicit enough
to unambiguously define a group of cells (a range) on a worksheet other than
the activesheet. Statement 2 however, is instructing VB to set a range in
ws3 (with €˜ws3.Range) using cell arguments from a different sheet, the
activesheet (activesheet.cells(2, x), activesheet.cells(lRow, x)). This
conflict produces the runtime error.
---------------------------------

So, functionally equivalent ? Yes and No.

Equivalent ? Yes and No.

---
Jay

--
Jay


"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

syntax question
 

ok, i think it's because ws4 is not the active sheet, so the 2nd one fails.

thanks jim and jay
--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i guess to be more exact:
this works
If Application.Sum(Range(ws4.Cells(2, x), ws4.Cells(lRow, x))) 0 Then

this doesn't

If Application.Sum(ws4.Range(Cells(2, x), Cells(lRow, x))) 0 Then

--


Gary


"Jim Cone" wrote in message
...

Gary,
If the ActiveSheet is ws3 both will work.
If the ActiveSheet is some other sheet both will blow up.
I guess in that regard they both function the same. <g

The preferred method is to prefix Range and Cells with the
worksheet...
ws3.Range(ws3.Cells(2, x), ws3.Cells(lRow, x))
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Gary Keramidas" <GKeramidasATmsn.com
wrote in message
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

syntax question
 

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

syntax question
 

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

syntax question
 

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

syntax question
 

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