Creating a name with VBA

[Guest]

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]

How about:

with activesheet
.range(.range(strstart), .range(strend)).name = "ResourceList"
end with

 

[Don Guillett]

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

 

[Alan]

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."

 

[Guest]

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.

 

[Tom Ogilvy]

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

 

[Dave Peterson]

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