Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a name with VBA
I have an obscure problem. I have simplified it down to the code below. I
am trying to create a named range with VBA but the range name has inverted commas around it so it will not work. For example, the code below creates a named range for ResourceList of: ="Sheet2!$A$1:$A$3" rather than =Sheet2!$A$1:$A$3 (with no inverted commas) "Sub test() Dim strStart As String Dim strEnd As String Dim strAddress As String strStart = "$A$1" strEnd = "$A$3" strAddress = ActiveWorkbook.Name & "!" & strStart & ":" & strEnd ActiveWorkbook.Names.Add Name:="ResourceList", RefersToR1C1:=strAddress End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a name with VBA
How about:
with activesheet .range(.range(strstart), .range(strend)).name = "ResourceList" end with NevilleT wrote: I have an obscure problem. I have simplified it down to the code below. I am trying to create a named range with VBA but the range name has inverted commas around it so it will not work. For example, the code below creates a named range for ResourceList of: ="Sheet2!$A$1:$A$3" rather than =Sheet2!$A$1:$A$3 (with no inverted commas) "Sub test() Dim strStart As String Dim strEnd As String Dim strAddress As String strStart = "$A$1" strEnd = "$A$3" strAddress = ActiveWorkbook.Name & "!" & strStart & ":" & strEnd ActiveWorkbook.Names.Add Name:="ResourceList", RefersToR1C1:=strAddress End Sub -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a name with VBA
If you are using direct cell references, this one line will do everything
your code is doing. Your code is more usefull when dealing with variable ranges (with some additional code added). Range("A1:A3").Name = "ResourceList" or ActiveWorkbook.Names.Add Name:="ResourceList", RefersToR1C1:= _ "=Sheet1!R1C1:R3C1" Alan "The only dumb question is a question left unasked." "NevilleT" wrote in message ... I have an obscure problem. I have simplified it down to the code below. I am trying to create a named range with VBA but the range name has inverted commas around it so it will not work. For example, the code below creates a named range for ResourceList of: ="Sheet2!$A$1:$A$3" rather than =Sheet2!$A$1:$A$3 (with no inverted commas) "Sub test() Dim strStart As String Dim strEnd As String Dim strAddress As String strStart = "$A$1" strEnd = "$A$3" strAddress = ActiveWorkbook.Name & "!" & strStart & ":" & strEnd ActiveWorkbook.Names.Add Name:="ResourceList", RefersToR1C1:=strAddress End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a name with VBA
Thanks to you all for such a quick response. Actually the response from Don
and Alan both do the trick. I should have been using worksheet rather than workbook but I had taken what was a complex part of another spreadsheet and tried to simplify it into a single sheet with one function. I am using variable addresses for the real application. Thanks again guys. "Alan" wrote: If you are using direct cell references, this one line will do everything your code is doing. Your code is more usefull when dealing with variable ranges (with some additional code added). Range("A1:A3").Name = "ResourceList" or ActiveWorkbook.Names.Add Name:="ResourceList", RefersToR1C1:= _ "=Sheet1!R1C1:R3C1" Alan "The only dumb question is a question left unasked." "NevilleT" wrote in message ... I have an obscure problem. I have simplified it down to the code below. I am trying to create a named range with VBA but the range name has inverted commas around it so it will not work. For example, the code below creates a named range for ResourceList of: ="Sheet2!$A$1:$A$3" rather than =Sheet2!$A$1:$A$3 (with no inverted commas) "Sub test() Dim strStart As String Dim strEnd As String Dim strAddress As String strStart = "$A$1" strEnd = "$A$3" strAddress = ActiveWorkbook.Name & "!" & strStart & ":" & strEnd ActiveWorkbook.Names.Add Name:="ResourceList", RefersToR1C1:=strAddress End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a name with VBA
You have received some workarounds, but you have two problems in your
original code: 1) you are passing an A1 Style reference and using the R1C1 property 2) you omitted the equal sign in the argument to RefersTo. The below works fine: Sub test() Dim strStart As String Dim strEnd As String Dim strAddress As String strStart = "$A$1" strEnd = "$A$3" strAddress = ActiveWorkbook.Name & "!" & strStart & ":" & strEnd ActiveWorkbook.Names.Add Name:="ResourceList", RefersTo:= "=" & strAddress End Sub -- Regards, Tom Ogilvy "NevilleT" wrote in message ... I have an obscure problem. I have simplified it down to the code below. I am trying to create a named range with VBA but the range name has inverted commas around it so it will not work. For example, the code below creates a named range for ResourceList of: ="Sheet2!$A$1:$A$3" rather than =Sheet2!$A$1:$A$3 (with no inverted commas) "Sub test() Dim strStart As String Dim strEnd As String Dim strAddress As String strStart = "$A$1" strEnd = "$A$3" strAddress = ActiveWorkbook.Name & "!" & strStart & ":" & strEnd ActiveWorkbook.Names.Add Name:="ResourceList", RefersToR1C1:=strAddress End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a name with VBA
If you're saying you wanted a worksheet level name, you could also use:
with activesheet .range(.range(strstart), .range(strend)).name _ = "'" & .name & "'!ResourceList" end with NevilleT wrote: Thanks to you all for such a quick response. Actually the response from Don and Alan both do the trick. I should have been using worksheet rather than workbook but I had taken what was a complex part of another spreadsheet and tried to simplify it into a single sheet with one function. I am using variable addresses for the real application. Thanks again guys. "Alan" wrote: If you are using direct cell references, this one line will do everything your code is doing. Your code is more usefull when dealing with variable ranges (with some additional code added). Range("A1:A3").Name = "ResourceList" or ActiveWorkbook.Names.Add Name:="ResourceList", RefersToR1C1:= _ "=Sheet1!R1C1:R3C1" Alan "The only dumb question is a question left unasked." "NevilleT" wrote in message ... I have an obscure problem. I have simplified it down to the code below. I am trying to create a named range with VBA but the range name has inverted commas around it so it will not work. For example, the code below creates a named range for ResourceList of: ="Sheet2!$A$1:$A$3" rather than =Sheet2!$A$1:$A$3 (with no inverted commas) "Sub test() Dim strStart As String Dim strEnd As String Dim strAddress As String strStart = "$A$1" strEnd = "$A$3" strAddress = ActiveWorkbook.Name & "!" & strStart & ":" & strEnd ActiveWorkbook.Names.Add Name:="ResourceList", RefersToR1C1:=strAddress End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
creating a new key | Excel Discussion (Misc queries) | |||
creating Yes,No | Excel Discussion (Misc queries) | |||
Creating Id | Excel Worksheet Functions | |||
Really need help creating pop ups | Excel Discussion (Misc queries) | |||
Creating a Log | Excel Programming |