SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2376722%22%20slang%3D%22en-US%22%3ESaving%20a%20cell's%20address%20in%20another%20cell%20%26amp%3B%20later%20going%20back%20to%20that%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376722%22%20slang%3D%22en-US%22%3E%3CP%3ELet's%20say%20my%20cursor%20is%20in%20cell%20D123.%26nbsp%3B%20I%20want%20to%20create%20a%20macro%20that%20will%20jump%20to%20A300%2C%20save%20%22D123%22%20in%20A300%2C%20so%20some%20calculations%2C%20and%20then%20go%20back%20to%20cell%20D123.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20Visual%20Basic%20skills%20have%20atrophied.%26nbsp%3B%20So%2C%20my%20questions%3A%3C%2FP%3E%3CUL%3E%3CLI%3EHow%20do%20I%20capture%20the%20cell%20address%20of%20the%20cursor%20when%20the%20macro%20is%20executed%3F%3C%2FLI%3E%3CLI%3EHow%20do%20I%20store%20that%20address%20in%20another%20cell%3F%3C%2FLI%3E%3CLI%3EHow%20do%20I%20take%20the%20cell%20address%20in%20one%20cell%20%26amp%3B%20use%20it%20to%20navigate%20to%20that%20cell%3F%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EP.S.%26nbsp%3B%20I%20used%20to%20know%20enouigh%20Visual%20Basic%20to%20do%20this%2C%20but%20I%20haven't%20programmed%20anything%20in%20a%20few%20years.%26nbsp%3B%20Let%20that%20be%20a%20lesson%20to%20all%20of%20us%20--%20keep%20your%20skills%20fresh.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2376722%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2376792%22%20slang%3D%22en-US%22%3ERe%3A%20Saving%20a%20cell's%20address%20in%20another%20cell%20%26amp%3B%20later%20going%20back%20to%20that%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376792%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F146587%22%20target%3D%22_blank%22%3E%40Jon%20Cohen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20don't%20really%20have%20to%20jump%20to%20A300%20to%20store%20a%20cell%20address%20there.%20In%20fact%2C%20you%20rarely%20need%20to%20select%20a%20call%20or%20range%20in%20a%20macro.%20It%20is%20perfectly%20possible%20to%20perform%20calculations%20and%20manipulate%20cells%20without%20selecting%20them.%20And%20you%20could%20also%20store%20the%20cell%20that%20is%20active%20when%20you%20start%20the%20macro%20(or%20its%20address)%20in%20a%20variable.%3C%2FP%3E%0A%3CP%3EIf%20you%20still%20need%20to%20store%20the%20address%20of%20the%20active%20cell%20in%20A300%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3E%20%20%20%20Range(%22A300%22).Value%20%3D%20ActiveCell.Address%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2376880%22%20slang%3D%22en-US%22%3ERe%3A%20Saving%20a%20cell's%20address%20in%20another%20cell%20%26amp%3B%20later%20going%20back%20to%20that%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376880%22%20slang%3D%22en-US%22%3EThanks%20--%20it's%20coming%20back%20to%20me.%20So%2C%20can%20I%20do%20something%20like...%3CBR%20%2F%3ECameFrom%20%3D%20ActiveCell.Address%3CBR%20%2F%3E%26lt%3B%20do%20my%20calculations%2C%20then%20%26gt%3B%3CBR%20%2F%3EActiveCell.Address%20%3D%20CameFrom%3CBR%20%2F%3E%3CBR%20%2F%3E%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2376909%22%20slang%3D%22en-US%22%3ERe%3A%20Saving%20a%20cell's%20address%20in%20another%20cell%20%26amp%3B%20later%20going%20back%20to%20that%20cell%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2376909%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F146587%22%20target%3D%22_blank%22%3E%40Jon%20Cohen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENo%2C%20Address%20is%20a%20read-only%20property%3A%20you%20cannot%20set%20it.%3C%2FP%3E%0A%3CP%3EYou%20use%20the%20Select%20method%20of%20a%20cell%20to%20make%20it%20the%20active%20cell.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20could%20do%20this%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%20%20%20%20Dim%20CameFrom%20As%20Range%0A%20%20%20%20Set%20CameFrom%20%3D%20ActiveCell%0A%20%20%20%20...%0A%20%20%20%20%3CYOUR%20calculations%3D%22%22%20here%3D%22%22%3E%0A%20%20%20%20...%0A%20%20%20%20CameFrom.Select%3C%2FYOUR%3E%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENote%20that%20CameFrom%20is%20a%20Range%20variable%20here%2C%20not%20a%20String%20variable.%3C%2FP%3E%3C%2FLINGO-BODY%3E
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 (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.