Named range reference in VBA for Excel 2007

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mike - 12 Sep 2007 20:41 GMT

I'm trying to refer to a named cell in a table in some VBA code for an
Excel 2007 workbook. I used the "create from selection" feature in
Excel 2007 to create the names for the range. The names are based on
the left columns and the top row, so I should be able to reference any
cell in the table by the name of the column and the name of the row.
For example: If the table had three rows named Bob, Linda, and Susan
and 3 columns named 2002, 2003, 2004 I should be able to reference a
cell by Linda 2002. Is this correct?

In the VBA I tried to reference a cell by the following code:
strCell = "Linda 2002"
Range(strCell).Value = "152.25"

When I attempt to run the code I get this error:
Run-Time Error 1004
Method 'Range' of object '_Global' failed

Any suggestions on what I'm doing wrong? Thanks.

Reply to this Message

Jim Rech - 12 Sep 2007 21:33 GMT

You cannot create a name using a number. If you check the defined named
list you will see that 2002, etc. was not created. If you convert the
numbers to text by preceding them with an apostrophe then you can use the
'create names from selection' feature successfully. The names created
though will be _2002, etc., which you will be able to use in a macro:

Range("Linda _2002").Value = "152.25"

Signature

Jim

| I'm trying to refer to a named cell in a table in some VBA code for an
| Excel 2007 workbook. I used the "create from selection" feature in
[quoted text clipped - 14 lines]
|
| Any suggestions on what I'm doing wrong? Thanks.

Reply to this Message