VBA - How Do I Return to a cell after running a sub

[Lidsavr]

I am running Excel 2007 and writing code that Excel 2003 users may execute.

I need some VBA help. I want my program to remember the address of an ActiveCell. I will then send the program off to execute other code and then I want it to come back to the same cell that was previously active.

I've tried

Set MyRange = ActiveCell

I've also tried

Set MyRange = Range.AddressLocal

I keep getting compile errors on both.

Am I on the right track or do I need to try another way?

Thank you for your help in advance.

Charles

 

[MrKowz]

Make sure to Dim the variable properly:

Public Sub Test()
Dim MyRange As Range
MyRange = ActiveCell
'your code here
MyRange.Select
End Sub

However, most code can be created that does not "select" any cells, since you can work directly with range objects in VBA.

 

[JackDanIce]

Play with this:

Sub SomethingsGoBumpInTheNight ()

Dim myRange as Range

Set myRange = Range("A1")
Cells(Rows.Count, Columns.Count).Select
MsgBox "Now to go to myRange as defined in the code"
myRange.Select
MsgBox "Ninja power"

End Sub

 

[Jonmo1]

I am running Excel 2007 and writing code that Excel 2003 users may execute.

I need some VBA help. I want my program to remember the address of an ActiveCell. I will then send the program off to execute other code and then I want it to come back to the same cell that was previously active.

I've tried

That shouldn't be giving compile errors, unless you have Option Explicit declared at the top of the module...
Then just declare MyRange As Range, as MrKowz shows.

However...
If your subsequent code changes sheets, then you will also need to set a variable to the original sheet, and select it first, then the range..

Dim MySheet As WorkSheet
Dim MyRange As Range
 
Set MySheet = ActiveSheet
Set MyRange = ActiveCell
 
run some code
 
MySheet.Select
MyRange.Select

However....
Ideally, your best bet is to write your subsequent code in such a way that it does not change which sheet/range is active..
You can write code to manipulate sheets/cells directly without selecting/activating them..


Hope that helps.

 

[Norie]

How about not leaving the cell that was active when the code was executed?

That could probably be possible, but hard to tell without seeing the code you are running.

If you really want to go back to it try this.

Set rngActive = ActiveCell
 
' your code
 
Appplication.GoTo rngActive, Scroll

 

[Jonmo1]

Nice Norie...
I always forget about GoTo Range..
That way the original sheet doesn't need to be active...

Although, I think you misspelled Appplication

 

[Norie]

No I didn't.

It was the evil keyboard genie.