Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem creating named ranges in a Macro!
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem creating named ranges in a Macro!
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem creating named ranges in a Macro!
Change this:
strRefersTo = Activesheet.Range("A1").value to this: strRefersTo = Activesheet.Range("A1").formula -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "LABKHAND" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating dynamic ranges which are named. | Excel Worksheet Functions | |||
problem in deleting the named ranges | Excel Worksheet Functions | |||
Problem with Dynamic Named Ranges | Excel Worksheet Functions | |||
Macro (Print) - Calling Named Ranges | Excel Discussion (Misc queries) | |||
Problem Working with Named Ranges | Excel Discussion (Misc queries) |