Why is c not a valid name?

[Guest]

I am trying to assign Named Ranges as follows:

Sub Macro2()
Range("A1").Select
ActiveWorkbook.Names.Add Name:="a", RefersToR1C1:="=Sheet2!R1C1"
Range("B1").Select
ActiveWorkbook.Names.Add Name:="b", RefersToR1C1:="=Sheet2!R1C2"
Range("C1").Select
ActiveWorkbook.Names.Add Name:="c", RefersToR1C1:="=Sheet2!R1C3"
End Sub

The last line of code raises a 1004 That name is not valid.


Why?

 

[Guest]

You're right, I didn't know that. When I select a cell or range on a
worksheet, then ckick in the name reference box and type in "c" Excel selects
the entire column of the active cell, rather than adds a named range "c".
Using the Insert->Name->Define menu option gives me that same error message
you got.

I guess you'll just have to use more descriptive names.

 

[Bob Phillips]

Which would suggest that c refers to the column would it not? In the same
way you cannot use r, must be something to do with R1C1.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

that is because technically the name 'c' is already taken
just like you can not name a range 'A1', 'B1' etc
C is assigned to the entire column c as the named range 'C' therefore that
name is already assigned.

 

[Tom Ogilvy]

You can't use any name that looks like a cell reference.

 

[Guest]

Thank you all very much.

I guess I won't ask why
Dim to as String
fails when
Dim too as String
works.

 

[Guest]

Excel must reserve "c" and "r" for the R1C1 notation style, though I must
admit that I've never seen that rule in any Excel book I've read. The
closest thing I found was in the help files, where it says "Names cannot be
the same as a cell reference, such as Z$100 or R1C1." In that vein, "R"
would refer to the entire row of the active cell and "C" the entire active
cell column.

 

[Guest]

How true! using "r" selected the row.

Which would suggest that c refers to the column would it not? In the same
way you cannot use r, must be something to do with R1C1.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

But c doesn't look like a 'cell' reference, otherwise any letter would fail.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

Yes, that would be asking "to" much "To" is a reserved keyword "For i = 1
To 10"

 

[Tom Ogilvy]

Sure it does, you said it yourself. It does in R1C1. Names of Names don't
change with your choice of cell addressing.

C means current column. R means current row.


--
Regards,
Tom Ogilvy

But c doesn't look like a 'cell' reference, otherwise any letter would fail.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)