Naming a Cell
 

I want to read through a workbook and on each worksheet
give the first nonblank cell in column 15 the local name
FirstValue. Count is a loop counter for the worksheets
and mRowCtr is a loop counter for the rows on a sheet.
Both the following generate error messages:

ActiveWorkbook.Names.Add Text:="FirstValue", _
RefersTo:="=Sheets(count)!Cells(mRowCtr,C15)"

ActiveWorkbook.Names.Add Name:="Sheets(count)!FirstValue",
_
RefersToR1C1:="=Sheets(count)!Cells(mRowCtr,C15)"

What is wrong? Any help appreciated

Naming a Cell
 

Thanks, it works great.

-----Original Message-----
Steve -

You have your formula mixed up with VBA variables and
methods, within
the quotes. Also, you are only going to end up with one
range named
"FirstValue", and it will be global to the workbook, not
local to each
sheet.

This is a quick way to add a sheet-level name to a given
sheet:

worksheets(count).cells(mRowCtr,15).name = _
"'" & worksheets(count).name & "'!FirstValue"

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______

Steve wrote:
I want to read through a workbook and on each worksheet
give the first nonblank cell in column 15 the local
name
FirstValue. Count is a loop counter for the worksheets
and mRowCtr is a loop counter for the rows on a sheet.
Both the following generate error messages:

ActiveWorkbook.Names.Add Text:="FirstValue", _
RefersTo:="=Sheets(count)!Cells(mRowCtr,C15)"

ActiveWorkbook.Names.Add Name:="Sheets(count)!
FirstValue",
_
RefersToR1C1:="=Sheets(count)!Cells(mRowCtr,C15)"

What is wrong? Any help appreciated

.