VBA to return to original cell

[51DegreesNorth]

Hi,

In my project, the Worksheet_Change event performs some actions. When all is done, I'd like to return the user to the cell where it all started, without firing the on change event again.

Dim Src_PO_Nr As Long
Dim DestRow As Long
Dim SrcRow As Long
Dim RetAdr As String


    'active cell determined by user (new entry or edit cell)
    Src_PO_Nr = Cells(ActiveCell.Row, 1)
    SrcRow = ActiveCell.Row
    RetAdr = ActiveCell.Address

    '
    Worksheets("Some Sheet").Activate
    
    DestRow = Application.WorksheetFunction.Match(Src_PO_Nr, Range("A:A"), 0)
    Sheets("Some Sheet").Rows(DestRow).Value = Sheets("Some other Sheet").Rows(SrcRow).Value
    
    Worksheets("Some other Sheet").Range("RetAdr").Offset(1).Select

The last line of code errors out. If the orginal active cell was A1, then RetAdr contains $A$1. Declaring RetAdr as range does not fix it.

I"d like to get rid of the error. Also I'd like a a more efficient way to locate Src_PO_Nr - searching in an entire column seems overkill.

Your help much appreciated.

 

[Joe4]

In a Worksheet_Change event procedure, the default "Target" cell is the cell that is originally changed that sets the whole code in motion.
So you should be able to just use:

Target.Select

to return to it.

Since this is a "Change" event, and not a "SelectionChange" event, simply selecting this cell should not trigger any code to run again, unless you also have "SelectionChange" code, in which case ou would use:

Application.EnableEvents = False
Target.Select
Application.EnableEvents = True

 

[51DegreesNorth]

Thank you.
No luck, unfortunately. A

 MsgBox Target

remains empty ..... I can not declare a public variable in event code so I'm basically
back to square one.

 

[Joe4]

Thank you.
No luck, unfortunately. A

 MsgBox Target

remains empty ..... I can not declare a public variable in event code so I'm basically
back to square one.

What are you trying to accomplish with this?

MsgBox Target

and where are you putting it?

I think a more helpful thing to look at would be this:

MsgBox Target.Address

What does that return?

Could please post the entire procedure, including the header row?
And what sheet module are you putting this procedure in?

 

[51DegreesNorth]

My apologies for the late reply. The problem no longer exists; management has put this project on hold. Thanks for your assistance though, I will certainly do more research , you have made me think !