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
|