How to get named range value?

[Jorge Ribeiro]

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

 

[Per Jessen]

Hi

Try this:

MyVar = Range("SpecialNumber").Value

Regards,
Per

 

[Mike H]

Try

MsgBox Range("SpecialNumber").Text

Mike

 

[Jurgen]

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

 

[Brian S.]

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").valu
In case it's workbook-scoped, you can use
application.range("SpecialNumber").valul


Previous Posts In This Thread:

How to get named range value?
Hell

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

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

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

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

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

I'me going mad!!

thanx in advance

regad

Jorge

Re: How to get named range value?
H

Try this

MyVar = Range("SpecialNumber").Valu

Regards
Per

RE: How to get named range value?
Tr

MsgBox Range("SpecialNumber").Tex

Mik

:

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").valu
In case it's workbook-scoped, you can use
application.range("SpecialNumber").valul



Submitted via EggHeadCafe - Software Developer Portal of Choice
Easy "NO SCRIPT" DataGrid Tooltips in ASP.NET
http://www.eggheadcafe.com/tutorial...f5c-0e18fa4e2e43/easy-no-script--datagri.aspx

 

[Brian S.]

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/tutorial...8-aac1f8c62141/auto-save-in-javascript-w.aspx