LinkBack URL
About LinkBacks
Morning,
I'm after a formula or macro to give me the cell reference of the cell that is selected (eg A1).
I need this reference to appear in another cell.
How is this done please?
Formula/Macro to give active cell reference in cell
Morning,
I'm after a formula or macro to give me the cell reference of the cell that is selected (eg A1).
I need this reference to appear in another cell.
How is this done please?
Hello,
To return the selected cell address to cell A1 for example, you could use
range("A1").Value=selection.address
To keep the cell updated, you could use the worksheet selection_change event. Right click on the worksheet tab, select view code, and enter the following
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A1").Value = Selection.Address End Sub
Last edited by sweep; 06-23-2014 at 03:07 AM.
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
Formula
Formula:
=CELL("address",A1)
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
OK thanks
Does that go in the sheet code? Have tried that and nothing is happening
You could put this in the ThisWorkbook code module
and then put the formula =ActiveCellAddress in a cell.Private Sub Workbook_SheetActivate(ByVal Sh As Object) Call Workbook_SheetSelectionChange(Sh, Selection) End Sub Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) ThisWorkbook.Names.Add Name:="ActiveCellAddress", RefersTo:="=""" & ActiveCell.Address(, , , True) & """" End Sub
Last edited by mikerickson; 06-23-2014 at 03:13 AM.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
mikerickson
Good that works - how do I change the formatting of the result so it shows just the reference without the filename and sheetname?
Change the arguments of the .Address property to suit your needs.
Sorry but being a noob I don't know how to?
This is solved now - many thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks