Formula that returns address of the active cell.

[Derek]

Hi Guys and Gals

Is there a formula in Excel that returns the active cell address (ie dynamically). Excel updates the activecell address in the Name Box dynamically as you make a selection but I cannot find a standard formula to access it. I know I can achieve this with code using the selection-change event but this action then disallows use of the Undo button - which I specifically want to avoid. Perhaps there is an add-in available??

thanks for any good ideas

Regards

Derek

 

[Peter_SSs]

Derek

As I understand your question, I think the answer is "No".

However, can you give us a bit of a wider picture of what you have and what you are trying to achieve and maybe some way can be found to do waht you want.

 

[Derek]

Hi Peter

Thanks for your reply.
Currently I have a selection-change event macro:
[A1].Value = Cells(14, Target.Column).Value
This identifies the column of the activecell so I can extract the value in row 14 of that column - and put it in A1.
A simple vlookup formula in a cell (formatted like a viewing pane) then searches a table for whatever is in A1 and displays the result. So as cells are selected, the viewing pane updates.
This works perfectly, but the selection-event disables the Undo button, and I am looking for a way to achieve the same result and still retain the Undo button option.

Data validation options are not suitable

It seems strange that Excel has info and cell formulas that return all sorts of interesting stuff (including the address of the last cell changed) but not the address of the activecell, because that could be so useful.

I could write my own function, but I suspect, since it operates like a macro, it will disable the Undo button too.

Regards
Derek

 

[Peter_SSs]

Derek

I can't think of a way to do that - but somebody else might come along with a suggestion.

 

[timorrill]

This works perfectly, but the selection-event disables the Undo button, and I am looking for a way to achieve the same result and still retain the Undo button option.

Try doing a google search for VBA Undo. There are several interesting options that may be of some help to you.

 

[Tom Urtis]

Something that MIGHT be an option for you is to scrap the Selection Change event code, and instead, place this formula in cell A1:

=INDIRECT(CELL("address"))


Now, if you just select a cell in row 14, cell A1 will not be updated, but if you either double-click a cell in row 14, or hit the F2 key, and then the Enter key, cell A1 will be updated by displaying the same value that is in the cell of row 14 (or any cell really) that you went into and out of Edit mode.

Depending on how you enter the formula in cell A1, you might get a circular reference error, but exit out of that, select any other cell, hit F2 and Enter, then you are good to go from there forward.

So hitting F2 and Enter is less convenient than simply selecting a cell, that's true, but with the above formula, you get to keep the Undo feature. Looks like it comes down to what you are willing to trade off...selecting vs F2 > Enter and no Undo vs keeping Undo.

 

[Derek]

Hi Tom

Thanks for replying. That is a rather intriguing solution Tom. It works exactly as you describe and gives the option of undo but I suspect the users of my current project may find it too cumbersome. Still it is another good tool to have in my arsenal.

Many thanks

Kind Regards
Derk

 

[Peter_SSs]

Derek

Given that you want to return to A1 the value from row 14 of the Active Cell column, see if these tweaks to Tom's idea get you close enough.

In A1, put this formula:
=OFFSET(INDIRECT(CELL("address")),14-CELL("row"),0)

Now select any cell/range, and to trigger the recalculation, you can use any of these ...

a) Tom's suggestions

b) Enter or delete any cell values.

c) Hit the F9 key

c) Click any column heading or row label divider.

e) Insert/Delete rows/columns/cells.

f) AutoFilter anywhere on the sheet.

 

[Derek]

Many thanks Peter, I will give it a try.

Kind regards

Derek

 

[GTO]

Howdy to all,

I'm probably missing something, but never minded a friendly jab so will ask. If the formula is better for undo, and only calculation seems problematic, would a forced calc be disadvantageous?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    [A1].Calculate
End Sub

Thanks,

Mark