Get a cell's name in VBA

[Pallavi Singh]

Hello,
Could anyone point out how to get a cell's name in the VBA code.
I am trying to retrieve a cell's name - for example suppose target is
the range object pointing to a single cell.
Following lines get the cell address instead of cell name.
target.Name or target.Cells(1, 1).Name
How do I get the cell's name instead of address?
Thanks in advance

 

[Leo Heuser]

Pallavi

You have to use the name property twice:

Target.Name.Name

 

[Pallavi Singh]

Leo,
Thanx for your quick response.
The problem is that 'Name' is being exposed as a property. Hence
when I do a 'targetRange.Name.Name' I get a runtime error 'Object
doesn't support this property or method', error 438. Do I need to set
some sort of property to True to make this work?
Thanx

 

[Leo Heuser]

You're welcome, Pallavi.

Please post the full code + which
version of Excel you are using to the group.

I have to leave at 9.00, so if your
posting is later than that, I won't be
able to answer until this afternoon.
(right now it's 7.30 in the morning in Denmark)

 

[Pallavi Singh]

Hi Leo,

Actually using targetRange.Name.Name did solve the problem.
I don't know why it din't work 1st time. But its working right now.
Thanks for your help

 

[Leo Heuser]

Glad it worked for you, Pallavi
Thanks for the feedback.

 

[mudasirahmed]

Hi Ms. Singh
I am kinda trying to do the same thing as you . Can you please sen
me the Macro code which finds the Cell Name.

Thanks
Muds

 

[Tom Ogilvy]

as was stated in the thread

set rng = Range("A1")
rng.name = "MyName"
msgbox rng.Name " is the address, the name is " & rng.Name.Name

if rng refers to a cell that does not have a name, and you do rng.name.name,
it will raise an error.

 

[preLude3]

And how can we avoid the error?