Problem creating named ranges in a Macro!

[LABKHAND]

Hello All,

My main purpose is to have a VB code that upon pressing a button, recreates
all my named ranges automatically. I appreciate all your help in this
matter. Here is my problem:

Using Excel 2003: Let's say, in the CONSTANT sheet, I have a cell A1 which
contains a reference to a cell location of another sheet in the same
workbook. I am trying to create a name range in the VB code.

Let's say Constant!A1 contains the ('='FY2009-FSA Health Tracker'!A2) string
value. In my VB code, I have the following summerized code:

Dim strRefersTo As String
{ I have code to select the CONSTANT sheet here....}
Then I have:
strRefersTo = Activesheet.Range("A1").value 'this string should have the
('='FY2009-FSA Health Tracker'!A2) value at this point.

Next I tried to assign it to a name range:
ActiveWorkbook.Names.Add Name:="TEST", RefersToR1C1:=strRefersTo

The code works, but when I check the ReferTo section of the TEST name range
in excel (INSERT->NAME->Define), it has the

value of: ='FY2009-FSA Health Tracker'!'A2'
Instead of: ='FY2009-FSA Health Tracker'!A2

How can I make this work?

Thanks

 

[Dave O]

I think the problem is here, or related to it:

strRefersTo = Activesheet.Range("A1").value
This assigns the resulting >value< of cell A1 to your variable

Try this instead:
strRefersTo = Activesheet.Range("A1").Formula
This assigns the >formula< instead of the value. It will include the
equal sign.

A little concerning to me: did you copy and paste the code here?
strRefersTo = Activesheet.Range("A1").value
Usually, "value" would appear with a capital V because it's a reserved
word.

Dave O'Brien
Eschew obfuscation

 

[Luke M]

Change this:
strRefersTo = Activesheet.Range("A1").value

to this:
strRefersTo = Activesheet.Range("A1").formula