Naming Ranges

[PCLIVE]

I'm having trouble naming a range with this code.
B1 to D7 is selected.
r1 = 1
A1 = "950"
Why isn't this working?

ActiveWorkbook.Names.Add Name:=Range("A" & r1).Value,
RefersTo:=Selection.Address


Thanks.

 

[PCLIVE]

Forgot to mention, the error is:

Run-time error '1004'"

Thant name is not valid.


Thanks,
Paul

 

[Dave Peterson]

You're trying to name a range 950.

Excel says that the name 950 looks to much like the number 950.

Maybe you can use:

name:="_"&range("a"&r1).value

(prepending an underscore)

=====
Imagine if you wrote
=950*2
and didn't get 1900 back!

 

[Peter T]

The error description is the answer!

Regards,
Peter T

PS defined names starting with a numeral are not valid

 

[PCLIVE]

Thanks,

I had just figured this out when you responded. However, I had orginally
been using a cobination of letters with numbers and the letters were in
front. I was still getting the error then, so I shortend the name to make
the inquiry simpler. After discovering that the name could not begin with a
number, I then realized that my original problem was caused by a space in
the cell value. Since Excel automatically substitutes spaces with
underscores when you manually create the named range, I did not notice that
this was the happening when trying to apply the same thing via VBA. Anyway,
it's working now.

Thanks,
Paul

 

[Dave Peterson]

Be careful with names that look too much like addresses, too.

A1 wouldn't work as a range name (while in A1 Reference style).