Access Cell name in VBA code
 

In an Excel worksheet I define a cell name as Angle_L. How do I access
the value of the cell with the name Angle_L in VBA code?

In a worksheet I have defined a cell with the name Angle_L and put the
value 10 into it.

I run this code in a VBA macro

Range("F2").Select
ActiveCell.Value = Angle_L

But the cell F2 is filled with the value 0, not the value contained in
the cell named Angle_L.

How can I get this to work?


--
ChrisSroka
------------------------------------------------------------------------
ChrisSroka's Profile: http://www.excelforum.com/member.php...o&userid=29168
View this thread: http://www.excelforum.com/showthread...hreadid=488965

Access Cell name in VBA code
 

Try:

Range("F2").value = range("Angle_L").value

Hope this helps
Rowan

ChrisSroka wrote:
In an Excel worksheet I define a cell name as Angle_L. How do I access
the value of the cell with the name Angle_L in VBA code?

In a worksheet I have defined a cell with the name Angle_L and put the
value 10 into it.

I run this code in a VBA macro

Range("F2").Select
ActiveCell.Value = Angle_L

But the cell F2 is filled with the value 0, not the value contained in
the cell named Angle_L.

How can I get this to work?

Access Cell name in VBA code
 

Const cANGEL_L As String = "Angle_L"
Sub demo()
Dim rAngleL As Range
Set rAngleL = Names.Item(cANGEL_L).RefersToRange

' to save a value to the cell
rAngleL.Value = 17
' to save a formula in the cell
rAngleL.FormulaR1C1 = "=R1C1 * 2"
' to read data from the cell
Dim myval As String
myval = rAngleL.Value



End Sub

"ChrisSroka" wrote:


In an Excel worksheet I define a cell name as Angle_L. How do I access
the value of the cell with the name Angle_L in VBA code?

In a worksheet I have defined a cell with the name Angle_L and put the
value 10 into it.

I run this code in a VBA macro

Range("F2").Select
ActiveCell.Value = Angle_L

But the cell F2 is filled with the value 0, not the value contained in
the cell named Angle_L.

How can I get this to work?


--
ChrisSroka
------------------------------------------------------------------------
ChrisSroka's Profile: http://www.excelforum.com/member.php...o&userid=29168
View this thread: http://www.excelforum.com/showthread...hreadid=488965

Access Cell name in VBA code
 

Patrick,

I tried the following code (cell F2 contains the number 150):
_________

Const cAngle As String = "Angle_L"

Private Sub CommandButton1_Click()

Dim rAngleL As Range
Set rAngleL = Names.Item("Angle_L").RefersToRange ' **runtime erro
here **

Range("F2").Value = rAngleL.Value

End Sub
___________

It compiled OK, but when I ran it I got a run time error 1004 with th
message "application-defined or object-defined error" when running th
marked line

Any ideas?

Chri

--
ChrisSrok
-----------------------------------------------------------------------
ChrisSroka's Profile: http://www.excelforum.com/member.php...fo&userid=2916
View this thread: http://www.excelforum.com/showthread.php?threadid=48896

Access Cell name in VBA code
 

Try dimming rAngleL as an object instead of a range

--
DataCollecto
-----------------------------------------------------------------------
DataCollector's Profile: http://www.hightechtalks.com/m36
View this thread: http://www.hightechtalks.com/t229652

Access Cell name in VBA code
 

Tried it, but same error occurs.


--
ChrisSroka
------------------------------------------------------------------------
ChrisSroka's Profile: http://www.excelforum.com/member.php...o&userid=29168
View this thread: http://www.excelforum.com/showthread...hreadid=488965

Access Cell name in VBA code
 

Have you definately got a named range Angle_L on your sheet?


ChrisSroka wrote:
Tried it, but same error occurs.

Access Cell name in VBA code
 

Angle_L is the name of a cell, not a range.

I defined it manually in another sheet in the workbook by the menu
sequence: Insert-Name-Define: Angle_L: return.

It's definitely there. I use it in cell formulas in other sheets in the
workbook.


--
ChrisSroka
------------------------------------------------------------------------
ChrisSroka's Profile: http://www.excelforum.com/member.php...o&userid=29168
View this thread: http://www.excelforum.com/showthread...hreadid=488965