On Jul 2, 10:27*am, wrote:
In Excel 2003, I am defining a named range with the statement"
* *ThisWorkbook.Names.Add Name:="MyName", _
* *RefersTo:=Worksheets("X").Range("$A$2:$A$33")
This statement execures fine. *

If *I follow this by the code:
* *Set r = ThisWorkbook.Names("MyName").RefersToRange
* *Debug.Print r.Address
It prints: *$A$2:$A$33

If I use
* For n = 1 To ThisWorkbook.Names.Count
* * * *Debug.Print ThisWorkbook.Names(n).Name & " -- " & _ * *
* * * * * * * ThisWorkbook.Names(n)
* * * *Next n
it prints: MyName -- =[MyWorkboonName]X!$A$2:$A$33

All this is as I expect.

HOWEVER:
* *1. *If I select A2:A33 the name doesn't show in the name window
* *2. *If I use
* * * * * Range(MyRange).Validation.Add Type:=xlValidateList, _ * * * * *
* * * * * * * * * *Formula1:="=MyName"
* * * *I get an error that MyName is not a valid name
* *3. *If in Excel I select Insert/Name/Define, MyName is not is the list *
* * * * of defined names

If I define the named range manually by selecting it and typing the name in
the name box then all of the above works fine. *Apparently the names.add
statement is defining the name in some other collection. *So, I guess the
names.add statement is wrong somehow.

How can I do it correctly?
Secondly, what is the statement I use actually doing. *It is clearly doing
something, but the wrong thing.

Thanks!!

I tested your macro to name the range just fine. I usually just use
Worksheets("X").Range("$A$2:$A$33"),name="MyName"