Finding the cell address in R1C1 notation 2

[shawnmandel]

How do I programmatically determine (or convert) the address of a specific cell in Excel into R1C1 notation including the current sheet?

For example, I need to find the address of the active cell in the active sheet in the following format:

Assumption: active cell is B2 in sheet ABC

Result should be: ABC!R2C2


Any help would be greatly appreciated.


Cheers,
Shawn

 

[PHV]

Something like this ?
ActiveCell.Address(, , xlR1C1, True)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[shawnmandel]

Great. I'm almost there.

The reason I needed this functionality, is to be able to programmatically assign a range name to that cell. For example:

CellAddy = ActiveCell.Address(, , xlR1C1, True)
ActiveWorkbook.Names.Add Name:="LastRow", RefersToR1C1:=CellAddy

I don't get any errors, but 'LastRow' doesn't appear to get properly assigned to the specific cell.

Any ideas?


Thanks so much for your response,
Shawn

 

[byundt]

Shawn,
You are missing an = sign in your "Refers to" string. Delete the name from the name list, then try it like:

CellAddy = ActiveCell.Address(, , xlR1C1, True)
ActiveWorkbook.Names.Add Name:="LastRow", RefersToR1C1:="=" & CellAddy

Brad