Retrieve the cell name

[meb135]

Hi,

I have several cells in column A in which i named them via the Insert/Names....
I want to loop through the column based on a condition and when that condition is met, to provide me with the name of the cell.

y = x
Do While Cells(y, 1).Value = Empty
y = y - 1
Loop
CellName = ?????? (How do i get the cell's name here?)

thanks

 

[VoG]

Try

y = x
Do While Cells(y, 1).Value = Empty
y = y - 1
Loop
CellName = Cells(y, 1).Name.Name

 

[Trevor G]

Do you mean Cell Address?

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> cellAdd()<br>Range("B5") = Range("A1").Cells.Address<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[GlennUK]

Like this:

CellName = Cells(y, 1).Name.Name

 

[meb135]

Thanks Vog.
That's what i was looking for.

 

[Weaver]

Like this:

CellName = Cells(y, 1).Name.Name

.name.name

"What's the name of my name?"

Who'd a thot it?

 

[GlennUK]

.name.name

"What's the name of my name?"

Who'd a thot it?

I know!!! I remember being gobsmacked the first time I found out about that.