Naming a Cell

[Steve]

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

 

[Steve]

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
_______


.