SOLVED

Saving a cell's address in another cell & later going back to that cell

Copper Contributor

Let's say my cursor is in cell D123.  I want to create a macro that will jump to A300, save "D123" in A300, so some calculations, and then go back to cell D123. 

 

My Visual Basic skills have atrophied.  So, my questions:

  • How do I capture the cell address of the cursor when the macro is executed?
  • How do I store that address in another cell?
  • How do I take the cell address in one cell & use it to navigate to that cell?

 

Thanks in advance.

 

P.S.  I used to know enouigh Visual Basic to do this, but I haven't programmed anything in a few years.  Let that be a lesson to all of us -- keep your skills fresh.

 

3 Replies

@Jon Cohen 

You don't really have to jump to A300 to store a cell address there. In fact, you rarely need to select a call or range in a macro. It is perfectly possible to perform calculations and manipulate cells without selecting them. And you could also store the cell that is active when you start the macro (or its address) in a variable.

If you still need to store the address of the active cell in A300:

 

    Range("A300").Value = ActiveCell.Address
Thanks -- it's coming back to me. So, can I do something like...
CameFrom = ActiveCell.Address
< do my calculations, then >
ActiveCell.Address = CameFrom

?
best response confirmed by Jon Cohen (Copper Contributor)
Solution

@Jon Cohen 

No, Address is a read-only property: you cannot set it.

You use the Select method of a cell to make it the active cell.

 

You could do this:

 

 

    Dim CameFrom As Range
    Set CameFrom = ActiveCell
    ...
    <your calculations here>
    ...
    CameFrom.Select

 

 

Note that CameFrom is a Range variable here, not a String variable.

1 best response

Accepted Solutions
best response confirmed by Jon Cohen (Copper Contributor)
Solution

@Jon Cohen 

No, Address is a read-only property: you cannot set it.

You use the Select method of a cell to make it the active cell.

 

You could do this:

 

 

    Dim CameFrom As Range
    Set CameFrom = ActiveCell
    ...
    <your calculations here>
    ...
    CameFrom.Select

 

 

Note that CameFrom is a Range variable here, not a String variable.

View solution in original post