MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Error when using variable to name a range

  Asked By: Harley    Date: Sep 13    Category: MS Office    Views: 974

I am trying to use a variable to name a range. The code is as follows:

' assign string from a cell to variable
LocCode = selection.cells(2,4).value
Selection.name = LocCode

This gives the "That name is not valid" error.

Anyone have any ideas on what to do?



5 Answers Found

Answer #1    Answered By: Andrew Levensky     Answered On: Sep 13

Selection.name = "LocCode"

Do you realise that
is the value in the cell  3 across to the right and one down from
selected cell?

Your code  will leave you with a variable  called LocCode containing
whatever was in that cell 3 across & 1 down, and a named range  called
LocCode on the active sheet.

Answer #2    Answered By: Ella Oliver     Answered On: Sep 13

Now I see what you're doing. Sorry. Make sure there's a string  in that
cell and that it doesn't have illegal characters. Names cannot include
spaces and cannot look like cell  references. There may be other
restrictions. Start with a simple, guaranteed legal name such as
'smithy' to test the code.

Answer #3    Answered By: Alok Iit     Answered On: Sep 13

While possibly tangent to the question at hand...

Selection() may be related to:
ActiveCell(x, y)
(they both have he same parameter format) which appears to be a form
of relative addressing.
However, it some sort of internal "_Default" construct and not to
be used in code  (according to help, or did I see this in MSDN) .
ActiveCell(1, 1) is the selected cell  - (0, 0) is one up and left].

I found this in some of my early code - must have stumbled across it
thinking it was the .Offset method.

To reference near-by cells 'relatively', use the offset method
ActiveCell.Offset(1, -2) is one down, two left.

I haven't been following the whole thread, but it soulds like a
Named Range is desired, but as a variable. Setting an Object,
perhaps. I'll butt out.

Answer #4    Answered By: Lucinda Hall     Answered On: Sep 13

I suppose that you want to Name your list as one of the strings in the
list itself? Correct?

Dim LocCode As String

LocCode = Cells(2, 1).Value 'This will choose the string.
ThisWorkbook.Names.Add Name:=LocCode, RefersTo:="=$A$1:$A$4",
Visible:=True 'this part will name the list as the string  chosen.

Answer #5    Answered By: Rosie Brooks     Answered On: Sep 13

Sorry guys, just realized my error. I was using a value that looks
like a cell  reference. The variable  LocCode was sometimes being
assigned the value "AL01" which Excel interprets as a range.

Thanks for everyone's help!

Didn't find what you were looking for? Find more on Error when using variable to name a range Or get search suggestion and latest updates.