How to get named range value?
 

Hello

I've a workbook with several named ranges defined. (Excel 2007)

I've a named range say SpecialNumber that refers to System!$a$20
My name is well formed because in name manager i can see it and using
it on a worksheet it functions great.

My problem is to retrieve the value pointed to by that named range

When i try Range("SpecialNumber") it reports an error.
Using thisWorkbook.Names("SpecialNumber").RefersTo returns =System!$a$20
and using thisWorkbook.Names("SpecialNumber").Value it returns the same
=System!$a$20

How can i retrieve the underlying value of the named range, that is, the
value of
cell $a$20 in sheet System

I'me going mad!!!

thanx in advance!

regads

Jorge

How to get named range value?
 

Hi

Try this:

MyVar = Range("SpecialNumber").Value

Regards,
Per

"Jorge Ribeiro" skrev i meddelelsen
...
Hello

I've a workbook with several named ranges defined. (Excel 2007)

I've a named range say SpecialNumber that refers to System!$a$20
My name is well formed because in name manager i can see it and using
it on a worksheet it functions great.

My problem is to retrieve the value pointed to by that named range

When i try Range("SpecialNumber") it reports an error.
Using thisWorkbook.Names("SpecialNumber").RefersTo returns =System!$a$20
and using thisWorkbook.Names("SpecialNumber").Value it returns the same
=System!$a$20

How can i retrieve the underlying value of the named range, that is, the
value of
cell $a$20 in sheet System

I'me going mad!!!

thanx in advance!

regads

Jorge

How to get named range value?
 

Try

MsgBox Range("SpecialNumber").Text

Mike

"Jorge Ribeiro" wrote:

Hello

I've a workbook with several named ranges defined. (Excel 2007)

I've a named range say SpecialNumber that refers to System!$a$20
My name is well formed because in name manager i can see it and using
it on a worksheet it functions great.

My problem is to retrieve the value pointed to by that named range

When i try Range("SpecialNumber") it reports an error.
Using thisWorkbook.Names("SpecialNumber").RefersTo returns =System!$a$20
and using thisWorkbook.Names("SpecialNumber").Value it returns the same
=System!$a$20

How can i retrieve the underlying value of the named range, that is, the
value of
cell $a$20 in sheet System

I'me going mad!!!

thanx in advance!

regads

Jorge

How to get named range value?
 

Bear in mind that named ranges have a scope which could be a worksheet or
the workbook.
in case it"s worksheet-scoped, you should be able to use something like
sheet1.range("SpecialNumber").value or sheets("Sheet
1").range("SpecialNumber").value
In case it's workbook-scoped, you can use
application.range("SpecialNumber").valule


"Jorge Ribeiro" wrote in message
...
Hello

I've a workbook with several named ranges defined. (Excel 2007)

I've a named range say SpecialNumber that refers to System!$a$20
My name is well formed because in name manager i can see it and using
it on a worksheet it functions great.

My problem is to retrieve the value pointed to by that named range

When i try Range("SpecialNumber") it reports an error.
Using thisWorkbook.Names("SpecialNumber").RefersTo returns =System!$a$20
and using thisWorkbook.Names("SpecialNumber").Value it returns the same
=System!$a$20

How can i retrieve the underlying value of the named range, that is, the
value of
cell $a$20 in sheet System

I'me going mad!!!

thanx in advance!

regads

Jorge

same problem
 

I've been having the exact same problem that Jorge described.

definitely something changed in 2007 that is not allowing the lookup of this value via the usual methods, though it doesn't seem well documented (I tried Help and could not find a sufficient answer).

mine are definitely workbook-scoped (the default). I will have to try the application.range instead.




Jurgen wrote:

Bear in mind that named ranges have a scope which could be a worksheet or the
23-Jan-09

Bear in mind that named ranges have a scope which could be a worksheet or
the workbook.
in case it"s worksheet-scoped, you should be able to use something like
sheet1.range("SpecialNumber").value or sheets("Sheet
1").range("SpecialNumber").value
In case it's workbook-scoped, you can use
application.range("SpecialNumber").valule


"Jorge Ribeiro" wrote in message
...

Previous Posts In This Thread:

On Friday, January 23, 2009 5:50 AM
JorgeRibeir wrote:

How to get named range value?
Hello

I've a workbook with several named ranges defined. (Excel 2007)

I've a named range say SpecialNumber that refers to System!$a$20
My name is well formed because in name manager i can see it and using
it on a worksheet it functions great.

My problem is to retrieve the value pointed to by that named range

When i try Range("SpecialNumber") it reports an error.
Using thisWorkbook.Names("SpecialNumber").RefersTo returns =System!$a$20
and using thisWorkbook.Names("SpecialNumber").Value it returns the same
=System!$a$20

How can i retrieve the underlying value of the named range, that is, the
value of
cell $a$20 in sheet System

I'me going mad!!!

thanx in advance!

regads

Jorge

On Friday, January 23, 2009 6:00 AM
Per Jessen wrote:

How to get named range value?
Hi

Try this:

MyVar = Range("SpecialNumber").Value

Regards,
Per

On Friday, January 23, 2009 6:13 AM
Mike wrote:

How to get named range value?
Try

MsgBox Range("SpecialNumber").Text

Mike

"Jorge Ribeiro" wrote:

On Friday, January 23, 2009 9:28 AM
Jurgen wrote:

Bear in mind that named ranges have a scope which could be a worksheet or the
Bear in mind that named ranges have a scope which could be a worksheet or
the workbook.
in case it"s worksheet-scoped, you should be able to use something like
sheet1.range("SpecialNumber").value or sheets("Sheet
1").range("SpecialNumber").value
In case it's workbook-scoped, you can use
application.range("SpecialNumber").valule


"Jorge Ribeiro" wrote in message
...


Submitted via EggHeadCafe - Software Developer Portal of Choice
Easy "NO SCRIPT" DataGrid Tooltips in ASP.NET
http://www.eggheadcafe.com/tutorials...--datagri.aspx

works
 

application.range("SpecialNumber").value --

this definitely works, as does application.range("SpecialNumber").select



Brian S. wrote:

same problem
30-Nov-09

I've been having the exact same problem that Jorge described.

definitely something changed in 2007 that is not allowing the lookup of this value via the usual methods, though it doesn't seem well documented (I tried Help and could not find a sufficient answer).

mine are definitely workbook-scoped (the default). I will have to try the application.range instead.

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice
Auto Save In JavaScript With window.setTimeout
http://www.eggheadcafe.com/tutorials...ascript-w.aspx