Naming a Cell in Excel

[sbev]

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

 

[VBAva]

For me, i would select the cell that i want to name and then use
ActiveCell.Name = "YourName"

just a thought

 

[TonyJollans]

Hi sbev,

A couple of problems. Firstly, unless C15 is a variable you should just use "15" in defining the Cells' column.

Secondly, You have variable names inside quotes so they will be passed to Excel as is and not interpreted by VBA first which is what you need. To provide a text string of the address you need to use the Name (or, better, Codename) of the Sheet and the Address of the Cell, something like ..

ActiveWorkbook.Names.Add

Name

:="FirstValue", _
RefersTo:=

"=" & Sheets(count).CodeName & "!" & Cells(mrowctr, 15).Address

And, finally, if you didn't notice in the code above, Text:= should be Name:=.

Enjoy,
Tony

 

[sbev]

Thanks for the help. I'm crunching along now.