Set Range.....am I to blind?

[carlo]

Hi All

I tried following simple code:
'-----------------------------------------------------------
dim copy_rng as range
set copy_rng = worksheets("sheet2").range(cells(1,1),cells(1,20))
'-----------------------------------------------------------

It only seems to work, when I activate Sheet2! I can't remember this
ever happened. I am not trying to select anything, so why does it have
to be active?
Do I become senile? This thing is driving me crazy!

thanks for any help

Carlo

 

[Ross Culver]

Which module are you working in?

Try using Sheets("Sheet2). instead of worksheets(...

Ross

 

[carlo]

Hi Ross, thank you for the fast answer!

I am in a module called "copy".
I first put the code into the worksheet directly, but
because I will copy from different worksheets i put
it in a Module.

I also tried Sheets already but the same problem occurs:
"Application-defined or Object-defined Error"

It should work, shouldn't it?

Thanks again

Carlo

 

[Jim Cone]

Carlo,
"cells" is not qualified and therefore refers to the active sheet.
Note the dots in the following...
With worksheets("sheet2")
set copy_rng = .range(.cells(1,1),.cells(1,20))
End With
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"carlo"
wrote in message
Hi All
I tried following simple code:
'-----------------------------------------------------------
dim copy_rng as range
set copy_rng = worksheets("sheet2").range(cells(1,1),cells(1,20))
'-----------------------------------------------------------
It only seems to work, when I activate Sheet2! I can't remember this
ever happened. I am not trying to select anything, so why does it have
to be active?
Do I become senile? This thing is driving me crazy!
thanks for any help
Carlo

 

[Basilisk96]

Do I become senile? This thing is driving me crazy!

I recall, I had this problem once...
You're fine, you just need some time AFK for a fresh perspective ;-)

Think about this, and you will see the light, I promise:
Who is the parent of your Cells() calls? That's right, qualify them
explicitly, and it will work in all cases.


Cheers,
-Basilisk96

 

[Ross Culver]

Try dropping the "set".

Hi Ross, thank you for the fast answer!

I am in a module called "copy".
I first put the code into the worksheet directly, but
because I will copy from different worksheets i put
it in a Module.

I also tried Sheets already but the same problem occurs:
"Application-defined or Object-defined Error"

It should work, shouldn't it?

Thanks again

Carlo

 

[carlo]

Thanks Basilisk96

I just tried a little bit more and i found out following:
'-------------------------------------------------------
dim copy_rng as range
set copy_rng = worksheets("sheet2").range("A1:T1") '<--works
set copy_rng = worksheets("sheet2").range(cells(1,1),cells(1,20)) '<--
works not
set copy_rng =
worksheets("sheet2").range(worksheets("sheet2").cells(1,1),worksheets("sheet2").cells(1,20))
'<-- works
'-------------------------------------------------------

i have to give Excel the parent to all the cells!
I thought the cells() in the range object are just for the addresses
sake, but I was wrong, they have to be on the same sheet, so I need to
specify the parentsheet for every cell.

thank you very much, sorry for the confusion.

cheers and thanks

Carlo

 

[carlo]

Thank you all for replying.

@Ross:
the Set is mandatory because copy_rng is an object!

Carlo

 

[Ross Culver]

Yeah. I never use objects, only cell/range references and variables. In
fact, if you would and when you have time, briefly explain the why bother to
use an object at all? I need to learn something new today.

Ross

 

[Chip Pearson]

The problem is that

worksheets("sheet2").range

refers to Sheet2 but

cells(1,1),cells(1,20)

refers to the active sheet, and if Sheet2 is not the active sheet, you will
be attempting to set a range on Sheet2 to cells on the active sheet. Fully
qualified, your code is interpreted as:

set copy_rng =
Worksheets("Sheet2").Range(ActiveSheet.Cells(1,1),ActiveSheet(1,20))

You should write your code as

With Worksheets("Sheet2")
Set copy_rng = .Range(.Cells(1,1),.Cells(1,20))
End With

Note that there is a leading period before "Range", and the two "Cells".
This is required for the With statement.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Chip Pearson]

the why bother to
use an object at all?

Because the entire object model -- the application's very definition -- is
comprised almost completely of objects, so using the built-in object types
allows your code to mesh seamlessly with the native Excel application. To
understand objects, you need to understand classes; an object is a class
that is loaded into memory. See http://www.cpearson.com/Excel/Classes.aspx
for an introduction to user created classes and objects.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[JLGWhiz]

Hi Ross, If you only run short macros for the active sheet, you probably
won't need to set object variables very much. But, if you are running
complex macros involving two or more sheets and two or more workbooks, then
setting object variables saves time creating the macro, improves efficiency
in running the macro and saves space in storing the macro.

 

[Basilisk96]

why bother to

use an object at all?

When writing complex code that spans multiple modules, classes, and
even VBA projects, objects make it easy to pass information between
those entities with the greatest of ease because you can pass the
objects around as arguments without losing data. It's a basic concept
in OOP.

If your code is short, simple, and resides all within one module then
you can get away without having to handle such ominous objectified
monstrosities.

Cheers,
-Basilisk96

 

[Ross Culver]

Thanks, guys. While I have had to write some lengthy code to handle a
series of conditions or workflow steps, I've never needed to do any really
complex coding nor work with multiple workbooks. I've always tried to
encourage the use of Excel as a reporting tool with an SQL data source, not
as a database; and in my industry (pipe, valve & flanges) there aren't many
complicated calculations.

Still, I'll keep this in mind for the future and do the research suggested
by Chip.

Thanks, again for your time.

Ross