How can I address via vb the value stored in a named range cell.

example: on worksheet 3 I have a cell named variable 1

I want to retrive the value from that cell without going to it and selecting
it. Is there a way?

Thanks

If Range(Variable_1) = "Blah" 'Spaces not allowed in named ranges
or
If [Variable_1] = "Blah"

HTH

Charles

Sandy wrote:
How can I address via vb the value stored in a named range cell.

example: on worksheet 3 I have a cell named variable 1

I want to retrive the value from that cell without going to it and selecting
it. Is there a way?

Thanks

Either
val = Worksheets("worksheet 3").Range("rangename").Value
or
val = Names("rangename").RefersToRange.Value

should work.

This assumes Worksheet 3 is in the active workbook. If not
then you need to qualify the above by, for examnple,
val = Workbooks("Workbook1").Names("rangename").RefersTo Range.Value




Sandy wrote:
How can I address via vb the value stored in a named range cell.

example: on worksheet 3 I have a cell named variable 1

I want to retrive the value from that cell without going to it and selecting
it. Is there a way?

Thanks

Depends on rather the range name is workbook level or worksheet level

Workbook level syntax:

Workbooks("Book1.xls").Worksheets("Sheet1").Range( "RangeName")

Worksheet level syntax:

Workbooks("Book1.xls").Worksheets("Sheet1").Range( "Sheet1!RangeName")

Or when the worksheet's name has at least 1 space in it and it's a worksheet
level range name.

Workbooks("Book1.xls").Worksheets("Sheet 1").Range("'Sheet 1'!RangeName")


--
Ronald R. Dodge, Jr.
Production Statistician/Programmer
Master MOUS 2000

"Sandy" wrote in message
...
How can I address via vb the value stored in a named range cell.

example: on worksheet 3 I have a cell named variable 1

I want to retrive the value from that cell without going to it and
selecting
it. Is there a way?

Thanks

good point, I forgot my quotes...
Range("Variable_1")

Charles

Andrew Taylor wrote:
Either
val = Worksheets("worksheet 3").Range("rangename").Value
or
val = Names("rangename").RefersToRange.Value

should work.

This assumes Worksheet 3 is in the active workbook. If not
then you need to qualify the above by, for examnple,
val = Workbooks("Workbook1").Names("rangename").RefersTo Range.Value




Sandy wrote:
How can I address via vb the value stored in a named range cell.

example: on worksheet 3 I have a cell named variable 1

I want to retrive the value from that cell without going to it and selecting
it. Is there a way?

Thanks

Sandy wrote:
How can I address via vb the value stored in a named range cell.

example: on worksheet 3 I have a cell named variable 1

No you don't; that's not a valid range name. If the range name of the
cell were variable1 (without the space) you can access it with
Range("variable1").value

Alan Beban


I want to retrive the value from that cell without going to it and selecting
it. Is there a way?

Thanks