Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range reference in VBA for Excel 2007
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range reference in VBA for Excel 2007
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" -- Jim "Mike" wrote in message oups.com... | 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2007 - link named excel range to word document | Excel Discussion (Misc queries) | |||
Problem with named range for a large spreadsheet in Excel 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 - Find formulas that use a named range | Excel Discussion (Misc queries) | |||
Vlookup using a reference to a named range?(Excel 2000) | Excel Discussion (Misc queries) | |||
automatic range - named range give me circular reference... | Excel Discussion (Misc queries) |