| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Referencing a named range based upon Range name entry in cell | Excel Worksheet Functions | |||
| Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
| inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
| Compare a selected Range with a Named range and select cells that do not exist | Excel Programming | |||
| If any cell in named range = 8 then shade named range | Excel Programming | |||
Linear Mode
