MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Formula that returns address of active cell?


Posted by Ben O. on July 02, 2001 9:32 AM

I would like a worksheet formula that returns the address of the cell that's currently selected. Is this possible? I know that the Name Box does this, but that can't be referenced in a formula (I don't think it can, at least) which is what I need to do.

Any help would be appreciated.

-Ben O.


Posted by Ben O. on July 02, 2001 9:59 AM

Follow-up question Re: Custom Functions

I'm now trying to create a custom function. I've never created one, so I'd appreciate any help.

Here's my function:

Function ActiveAddress(ws As Worksheet)
Set ActiveAddress = Sheets(ws).ActiveCell.Address
End Function

When I try to access it on a worksheet (=ACTIVEADDRESS("Sheet1")) I get a #VALUE error. What am I doing wrong?

-Ben

Posted by faster on July 02, 2001 12:02 PM

This function takes no arguments:

Function ActiveAddress()
ActiveAddress = ActiveCell.Address
End Function

Posted by Russell on July 02, 2001 4:32 PM

Re: Follow-up question Re: Custom Functions

Ben,

Your function is expecting an object of type Worksheet, and you are passing a string. If you change the ws as Worksheet to wsName as String, then it would probably work.

-Russell