Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save a cell location and return to it
How do I save a cells location in VB so I can chnge the active cell to
another sheet and then return later on the the saved cells location. Thanks Adam |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save a cell location and return to it
Hi Adam,
Try something like: '============= Public Sub Tester001() Dim rng As Range Set rng = ActiveCell 'your code, e.g.: ActiveSheet.Next.Select Application.Goto rng End Sub '<<============= --- Regards, Norman "Adam" wrote in message ... How do I save a cells location in VB so I can chnge the active cell to another sheet and then return later on the the saved cells location. Thanks Adam |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save a cell location and return to it
Thanks Norman works well!
"Norman Jones" wrote: Hi Adam, Try something like: '============= Public Sub Tester001() Dim rng As Range Set rng = ActiveCell 'your code, e.g.: ActiveSheet.Next.Select Application.Goto rng End Sub '<<============= --- Regards, Norman "Adam" wrote in message ... How do I save a cells location in VB so I can chnge the active cell to another sheet and then return later on the the saved cells location. Thanks Adam |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save a cell location and return to it
Hi Norman, Would you dissect this line for me. ActiveSheet.Next.Select Is it saying on the next active sheet select? Application.Goto rng That line don't work for me. 'ActiveSheet.Next.Select' I get: 'Object Variable or With Block Variable not set' But if i comment that line out it works. Thx Dave Norman Jones Wrote: Hi Adam, Try something like: '============= Public Sub Tester001() Dim rng As Range Set rng = ActiveCell 'your code, e.g.: ActiveSheet.Next.Select Application.Goto rng End Sub '<<============= --- Regards, Norman "Adam" wrote in message ... How do I save a cells location in VB so I can chnge the active cell to another sheet and then return later on the the saved cells location. Thanks Adam -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=498658 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save a cell location and return to it
Hi Dave,
The instruction: ActiveSheet.Next.Select selects (as you might intuitively anticipate) the next sheet. If, as I suspect in your case, the active sheet is the last sheet, then the expression will generate your encountered error. Of course, in real code, provision would be made to handle this eventuality but, here, the instruction was merely used to to effect an intentional change of selection and any other selection instruction might equally well have been used. The intention was merely to demonstrate the returm to a given location after an intervening selection. --- Regards, Norman "Desert Piranha" <Desert.Piranha.21rnca_1137479700.9615@excelforu m-nospam.com wrote in message news:Desert.Piranha.21rnca_1137479700.9615@excelfo rum-nospam.com... Hi Norman, Would you dissect this line for me. ActiveSheet.Next.Select Is it saying on the next active sheet select? Application.Goto rng That line don't work for me. 'ActiveSheet.Next.Select' I get: 'Object Variable or With Block Variable not set' But if i comment that line out it works. Thx Dave Norman Jones Wrote: Hi Adam, Try something like: '============= Public Sub Tester001() Dim rng As Range Set rng = ActiveCell 'your code, e.g.: ActiveSheet.Next.Select Application.Goto rng End Sub '<<============= --- Regards, Norman "Adam" wrote in message ... How do I save a cells location in VB so I can chnge the active cell to another sheet and then return later on the the saved cells location. Thanks Adam -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=498658 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save a cell location and return to it
Hi Norman, Thanks for the in depth explanation. I understand it now. You are correct in your suspicion, that the active sheet before that line, was the last one. Thanks again Dave Norman Jones Wrote: Hi Dave, The instruction: ActiveSheet.Next.Select selects (as you might intuitively anticipate) the next sheet. If, as I suspect in your case, the active sheet is the last sheet, then the expression will generate your encountered error. Of course, in real code, provision would be made to handle this eventuality but, here, the instruction was merely used to to effect an intentional change of selection and any other selection instruction might equally well have been used. The intention was merely to demonstrate the returm to a given location after an intervening selection. --- Regards, Norman "Desert Piranha" <Desert.Piranha.21rnca_1137479700.9615@excelforu m-nospam.com wrote in message news:Desert.Piranha.21rnca_1137479700.9615@excelfo rum-nospam.com... Hi Norman, Would you dissect this line for me. ActiveSheet.Next.Select Is it saying on the next active sheet select? Application.Goto rng That line don't work for me. 'ActiveSheet.Next.Select' I get: 'Object Variable or With Block Variable not set' But if i comment that line out it works. Thx Dave Norman Jones Wrote: Hi Adam, Try something like: '============= Public Sub Tester001() Dim rng As Range Set rng = ActiveCell 'your code, e.g.: ActiveSheet.Next.Select Application.Goto rng End Sub '<<============= --- Regards, Norman "Adam" wrote in message ... How do I save a cells location in VB so I can chnge the active cell to another sheet and then return later on the the saved cells location. Thanks Adam -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=498658 -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=498658 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save a cell location and return to it
Hi Dave,
The instruction: ActiveSheet.Next.Select selects (as you might intuitively anticipate) the next sheet. If, as I suspect in your case, the active sheet is the last sheet, then the expression will generate your encountered error. Of course, in real code, provision would be made to handle this eventuality but, here, the instruction was merely used to to effect an intentional change of selection and any other selection instruction might equally well have been used. The intention was merely to demonstrate the return to a given location after an intervening selection. The following example uses ActiveSheet.Next to cycle through the sheets of the active workbbook and restart at the first sheet when the last sheet is reached: '============= Sub CycleSheets() With ActiveSheet IIf(.Index < Sheets.Count, .Next, Sheets(1)).Select End With End Sub '<<============= --- Regards, Norman "Desert Piranha" <Desert.Piranha.21rnca_1137479700.9615@excelforu m-nospam.com wrote in message news:Desert.Piranha.21rnca_1137479700.9615@excelfo rum-nospam.com... Hi Norman, Would you dissect this line for me. ActiveSheet.Next.Select Is it saying on the next active sheet select? Application.Goto rng That line don't work for me. 'ActiveSheet.Next.Select' I get: 'Object Variable or With Block Variable not set' But if i comment that line out it works. Thx Dave Norman Jones Wrote: Hi Adam, Try something like: '============= Public Sub Tester001() Dim rng As Range Set rng = ActiveCell 'your code, e.g.: ActiveSheet.Next.Select Application.Goto rng End Sub '<<============= --- Regards, Norman "Adam" wrote in message ... How do I save a cells location in VB so I can chnge the active cell to another sheet and then return later on the the saved cells location. Thanks Adam -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=498658 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save a cell location and return to it
Hi Norman, Thanks for the example. Adam Didn't mean to hijack your thread, but i think you solved you query. Dave Norman Jones Wrote: Hi Dave, The instruction: ActiveSheet.Next.Select selects (as you might intuitively anticipate) the next sheet. If, as I suspect in your case, the active sheet is the last sheet, then the expression will generate your encountered error. Of course, in real code, provision would be made to handle this eventuality but, here, the instruction was merely used to to effect an intentional change of selection and any other selection instruction might equally well have been used. The intention was merely to demonstrate the return to a given location after an intervening selection. The following example uses ActiveSheet.Next to cycle through the sheets of the active workbbook and restart at the first sheet when the last sheet is reached: '============= Sub CycleSheets() With ActiveSheet IIf(.Index < Sheets.Count, .Next, Sheets(1)).Select End With End Sub '<<============= --- Regards, Norman "Desert Piranha" <Desert.Piranha.21rnca_1137479700.9615@excelforu m-nospam.com wrote in message news:Desert.Piranha.21rnca_1137479700.9615@excelfo rum-nospam.com... Hi Norman, Would you dissect this line for me. ActiveSheet.Next.Select Is it saying on the next active sheet select? Application.Goto rng That line don't work for me. 'ActiveSheet.Next.Select' I get: 'Object Variable or With Block Variable not set' But if i comment that line out it works. Thx Dave Norman Jones Wrote: Hi Adam, Try something like: '============= Public Sub Tester001() Dim rng As Range Set rng = ActiveCell 'your code, e.g.: ActiveSheet.Next.Select Application.Goto rng End Sub '<<============= --- Regards, Norman "Adam" wrote in message ... How do I save a cells location in VB so I can chnge the active cell to another sheet and then return later on the the saved cells location. Thanks Adam -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=498658 -- Desert Piranha ------------------------------------------------------------------------ Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934 View this thread: http://www.excelforum.com/showthread...hreadid=498658 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
a formula that return the cell location in a range | Excel Discussion (Misc queries) | |||
Macro - save to current location vs excel default location | Excel Discussion (Misc queries) | |||
How do I return the cell location | Excel Discussion (Misc queries) | |||
how do I return tthe location (cell) when using the max function | Excel Discussion (Misc queries) | |||
Return the Row of a Variable's location | Excel Discussion (Misc queries) |