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

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

try this. Did you mean workbook or worksheet?

Sub makename1()
Dim strStart As String
Dim strEnd As String
Dim strAddress As String
strStart = "$A$1"
strEnd = "$A$3"
strAddress = ActiveSheet.Name & "!" & strStart & ":" & strEnd
range(strAddress).Name = "RL3"
End Sub

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"NevilleT" <(E-Mail Removed)> wrote in message
news:04FE23F3-4969-48EF-B52E-(E-Mail Removed)...
>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
>

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" <(E-Mail Removed)> wrote in message
news:04FE23F3-4969-48EF-B52E-(E-Mail Removed)...
>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
>

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" <(E-Mail Removed)> wrote in message
> news:04FE23F3-4969-48EF-B52E-(E-Mail Removed)...
> >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
> >
>
>
>

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" <(E-Mail Removed)> wrote in message
news:04FE23F3-4969-48EF-B52E-(E-Mail Removed)...
>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
>

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" <(E-Mail Removed)> wrote in message
> > news:04FE23F3-4969-48EF-B52E-(E-Mail Removed)...
> > >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