Named range questions

T

Tetsuya Oguma

Hi all

I am a bit ocnfused with Names property. Please answer the following short questions
1. When to use local names and global names? I understand that If the same name is used for both locally and globally local name only exists
2. I have a named range "test" on Sheet1, with its 'referto' of "=Sheet1!$A$1:$A$5", that I can see in Insert|Name|Define... from Menu Bar. Then, in standard module (not behind sheet or class module) I have the following code to select the range
Range("test").Selec

This give me an error of Run-tim error '1004': Select method of Range failed. I really want to know why!!! How can I select the range (or manipulate cells on the same sheet, to my point) if activesheet is not the sheet that has that range

I am using Excel 2000 SR-1
 
C

chris

You can't use select on an inactive sheet. Just set a range object to the value then u can manipulate it

Set MyRng = Range("test"

----- Tetsuya Oguma wrote: ----

Hi all

I am a bit ocnfused with Names property. Please answer the following short questions
1. When to use local names and global names? I understand that If the same name is used for both locally and globally local name only exists
2. I have a named range "test" on Sheet1, with its 'referto' of "=Sheet1!$A$1:$A$5", that I can see in Insert|Name|Define... from Menu Bar. Then, in standard module (not behind sheet or class module) I have the following code to select the range
Range("test").Selec

This give me an error of Run-tim error '1004': Select method of Range failed. I really want to know why!!! How can I select the range (or manipulate cells on the same sheet, to my point) if activesheet is not the sheet that has that range

I am using Excel 2000 SR-1
 
T

Tom Ogilvy

You can't select something if the sheet isn't active. Either activate
sheet1 first or use Application.Goto


You use local names when it is appropriate to your purpose.

You can have one global name and more than one local name with the same base
name. The local name takes precedence or masks the global name when
referenced on the sheet where it is local - similar to local variables and
global variables.

--
Regards,
Tom Ogilvy


Tetsuya Oguma said:
Hi all,

I am a bit ocnfused with Names property. Please answer the following short questions.
1. When to use local names and global names? I understand that If the same
Click to expand...
name is used for both locally and globally local name only exists.
2. I have a named range "test" on Sheet1, with its 'referto' of
Click to expand...
"=Sheet1!$A$1:$A$5", that I can see in Insert|Name|Define... from Menu Bar.
Then, in standard module (not behind sheet or class module) I have the
following code to select the range:
Range("test").Select

This give me an error of Run-tim error '1004': Select method of Range
Click to expand...
failed. I really want to know why!!! How can I select the range (or
manipulate cells on the same sheet, to my point) if activesheet is not the
sheet that has that range?
 
C

chris

You also need to fully qualify the name Range with the sheet objec
Set MyRng = Sheets("Sheet1").Range("test"

----- Tetsuya Oguma wrote: ----

Hi all

I am a bit ocnfused with Names property. Please answer the following short questions
1. When to use local names and global names? I understand that If the same name is used for both locally and globally local name only exists
2. I have a named range "test" on Sheet1, with its 'referto' of "=Sheet1!$A$1:$A$5", that I can see in Insert|Name|Define... from Menu Bar. Then, in standard module (not behind sheet or class module) I have the following code to select the range
Range("test").Selec

This give me an error of Run-tim error '1004': Select method of Range failed. I really want to know why!!! How can I select the range (or manipulate cells on the same sheet, to my point) if activesheet is not the sheet that has that range

I am using Excel 2000 SR-1
 
T

Tom Ogilvy

Not in a general module if it is a workbook level name.

--
Regards,
Tom Ogilvy



chris said:
You also need to fully qualify the name Range with the sheet object
Set MyRng = Sheets("Sheet1").Range("test")

----- Tetsuya Oguma wrote: -----

Hi all,

I am a bit ocnfused with Names property. Please answer the following short questions.
1. When to use local names and global names? I understand that If the
Click to expand...
same name is used for both locally and globally local name only exists.
2. I have a named range "test" on Sheet1, with its 'referto' of
Click to expand...
"=Sheet1!$A$1:$A$5", that I can see in Insert|Name|Define... from Menu Bar.
Then, in standard module (not behind sheet or class module) I have the
following code to select the range:
Range("test").Select

This give me an error of Run-tim error '1004': Select method of Range
Click to expand...
failed. I really want to know why!!! How can I select the range (or
manipulate cells on the same sheet, to my point) if activesheet is not the
sheet that has that range?
 
Top