Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return cursor to previous cell
I'm using Excel 2000
It seems to be incapable of creating a relative range name during a macro. Even clicking the "relative" option still results in recording an absolute reference to the cell referenced during macro creation, rather than the keystrokes used to produce it so as to name the cell the cursor is in when the macro is executed. This inability has stymied so many little devices I've tried to implement and I'm really getting jack of the inability to do so many things I was doing with spreadsheets in Lotus 123 and Enable way back in the 80's! Obviously, I have to resort to Vbasic code. Can someone assist? I would be so grateful! Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return cursor to previous cell
How about you show us the absolute reference you have and tell us what you
want? "Kevryl" wrote: I'm using Excel 2000 It seems to be incapable of creating a relative range name during a macro. Even clicking the "relative" option still results in recording an absolute reference to the cell referenced during macro creation, rather than the keystrokes used to produce it so as to name the cell the cursor is in when the macro is executed. This inability has stymied so many little devices I've tried to implement and I'm really getting jack of the inability to do so many things I was doing with spreadsheets in Lotus 123 and Enable way back in the 80's! Obviously, I have to resort to Vbasic code. Can someone assist? I would be so grateful! Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return cursor to previous cell
Hi Barb,
Thanks for your interest. It could be any range, any range name, as long as when the macro is run, it creates the rangename relative to where the cursor is then, rather than to where it was when it was recorded. But OK, here is a typical simple example, similar to a function I have used in the past for continuously creating pay advices: 1) start recording the macro, 2) Go to the range "Anchor" (it matters not what the reference is because it will be different each time - see below step 10) 3) Select "relative" and select the range that includes 8 columns to the right and 6 rows down ([end][right and [end]down] are a tidy way of selecting these) 5) copy the contents to the clipboard 6) move cursor 8 rows down and paste 7) return to "Anchor". 8) Erase the rangename "Anchor" 9) Move cursor 8 rows down 10) Create range name "Anchor" 11) Stop recording Such a macro works perfectly in the old Lotus and Enable (a Lotus lookalike) because they record the keystrokes, rather than converting the results of the keystrokes to VB before recording. In Excel, the reverse is true and the "Relative" selection on the macro tool bar is ignored. To try and clarify the problem further: The first time the macro records the rangename "Anchor", if I'm on cell A1 then every time I run the macro it will return the cursor to cell A1 to recreate the name "Anchor", rather than moving the cursor 8 rows down to extend the form. There were so many of these commands that were very easy to use back in the 80's software mentioned. Another nice one was automated cursor movement with a "wait for input" option (so you could string together a whole automated sequence of data input anywhere on the worksheet). All these were on a convenient reference card that came with the software. These days you have to learn Visual Basic to do them. Anyway, I digress :-). The question is whether you can give me some simple macro code for creating a rangename relative to where the cursor is when the macro is run, rather than to where it was when it was recorded. Thanks again for your interest in my question. :-) Cheers "Barb Reinhardt" wrote: How about you show us the absolute reference you have and tell us what you want? "Kevryl" wrote: I'm using Excel 2000 It seems to be incapable of creating a relative range name during a macro. Even clicking the "relative" option still results in recording an absolute reference to the cell referenced during macro creation, rather than the keystrokes used to produce it so as to name the cell the cursor is in when the macro is executed. This inability has stymied so many little devices I've tried to implement and I'm really getting jack of the inability to do so many things I was doing with spreadsheets in Lotus 123 and Enable way back in the 80's! Obviously, I have to resort to Vbasic code. Can someone assist? I would be so grateful! Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return cursor to previous cell
Kevryl,
I'm not sure how to record this, but the following macro will place your range name "anchor" at the active cell: Sub test() Dim mRange As Name For Each mRange In ActiveWorkbook.Names If mRange.Name = "anchor" Then mRange.Delete Next ActiveWorkbook.Names.Add Name:="anchor", RefersTo:=ActiveCell End Sub "Kevryl" wrote: Hi Barb, Thanks for your interest. It could be any range, any range name, as long as when the macro is run, it creates the rangename relative to where the cursor is then, rather than to where it was when it was recorded. But OK, here is a typical simple example, similar to a function I have used in the past for continuously creating pay advices: 1) start recording the macro, 2) Go to the range "Anchor" (it matters not what the reference is because it will be different each time - see below step 10) 3) Select "relative" and select the range that includes 8 columns to the right and 6 rows down ([end][right and [end]down] are a tidy way of selecting these) 5) copy the contents to the clipboard 6) move cursor 8 rows down and paste 7) return to "Anchor". 8) Erase the rangename "Anchor" 9) Move cursor 8 rows down 10) Create range name "Anchor" 11) Stop recording Such a macro works perfectly in the old Lotus and Enable (a Lotus lookalike) because they record the keystrokes, rather than converting the results of the keystrokes to VB before recording. In Excel, the reverse is true and the "Relative" selection on the macro tool bar is ignored. To try and clarify the problem further: The first time the macro records the rangename "Anchor", if I'm on cell A1 then every time I run the macro it will return the cursor to cell A1 to recreate the name "Anchor", rather than moving the cursor 8 rows down to extend the form. There were so many of these commands that were very easy to use back in the 80's software mentioned. Another nice one was automated cursor movement with a "wait for input" option (so you could string together a whole automated sequence of data input anywhere on the worksheet). All these were on a convenient reference card that came with the software. These days you have to learn Visual Basic to do them. Anyway, I digress :-). The question is whether you can give me some simple macro code for creating a rangename relative to where the cursor is when the macro is run, rather than to where it was when it was recorded. Thanks again for your interest in my question. :-) Cheers "Barb Reinhardt" wrote: How about you show us the absolute reference you have and tell us what you want? "Kevryl" wrote: I'm using Excel 2000 It seems to be incapable of creating a relative range name during a macro. Even clicking the "relative" option still results in recording an absolute reference to the cell referenced during macro creation, rather than the keystrokes used to produce it so as to name the cell the cursor is in when the macro is executed. This inability has stymied so many little devices I've tried to implement and I'm really getting jack of the inability to do so many things I was doing with spreadsheets in Lotus 123 and Enable way back in the 80's! Obviously, I have to resort to Vbasic code. Can someone assist? I would be so grateful! Thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return cursor to previous cell
Hi Kevryl,
'------------------ It could be any range, any range name, as long as when the macro is run, it creates the rangename relative to where the cursor is then, rather than to where it was when it was recorded. But OK, here is a typical simple example, similar to a function I have used in the past for continuously creating pay advices: 1) start recording the macro, 2) Go to the range "Anchor" (it matters not what the reference is because it will be different each time - see below step 10) 3) Select "relative" and select the range that includes 8 columns to the right and 6 rows down ([end][right and [end]down] are a tidy way of selecting these) 5) copy the contents to the clipboard 6) move cursor 8 rows down and paste 7) return to "Anchor". 8) Erase the rangename "Anchor" 9) Move cursor 8 rows down 10) Create range name "Anchor" 11) Stop recording Such a macro works perfectly in the old Lotus and Enable (a Lotus lookalike) because they record the keystrokes, rather than converting the results of the keystrokes to VB before recording. In Excel, the reverse is true and the "Relative" selection on the macro tool bar is ignored. To try and clarify the problem further: The first time the macro records the rangename "Anchor", if I'm on cell A1 then every time I run the macro it will return the cursor to cell A1 to recreate the name "Anchor", rather than moving the cursor 8 rows down to extend the form. There were so many of these commands that were very easy to use back in the 80's software mentioned. Another nice one was automated cursor movement with a "wait for input" option (so you could string together a whole automated sequence of data input anywhere on the worksheet). All these were on a convenient reference card that came with the software. These days you have to learn Visual Basic to do them. Anyway, I digress :-). The question is whether you can give me some simple macro code for creating a rangename relative to where the cursor is when the macro is run, rather than to where it was when it was recorded. '------------------ It is rarely necessary, or desirable, to make selections and is usually inefficient to do so, If no selections are made, then there is no need to return the cursor to an initial position. Try something like: '============ Public Sub Tester() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Dim destRng As Range Set WB = Workbooks("MyBook.xls") '<<==== CHANGE Set SH = WB.Sheets("Sheet1") '<<==== CHANGE Set Rng = SH.Range("Anchor") Set destRng = Rng.Offset(8) Rng.Resize(7, 9).Copy Destination:=destRng destRng.Name = "Anchor" End Sub '<<============ I have assumed that the data range to be copied comprises 7 rows x 9 columns. If, in fact, this should be 6 rows x 8 columns, replace: Rng.Resize(7, 9).Copy Destination:=destRng with Rng.Resize(6, 8).Copy Destination:=destRng --- Regards, Norman |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return cursor to previous cell
"It is rarely necessary, or desirable, to make selections and is usually
inefficient to do so, If no selections are made, then there is no need to return the cursor to an initial position. " This is true where a sheet is static and everything can be handled with formulae. In a dynamic, self-expanding sheet you can select with the cursor, or direct to a rangename, but both have the same result (and in effect are both selections anyway), and the latter is where you run into trouble with absolute v relative. Where, for example a dynamic sheet is time-relative so that it ages and the whole process relocates, then selecting becomes both necessary and desirable. I have written a number of highly complex spreadsheets systems that rely upon this capability and work very efficiently. Most of them were before the days of Excel though, and back in the days where, with only 640k to play with one had to be efficient in spreadsheet design. Thanks for the code. I'll copy and paste that into a "hold" file that I keep for this kind of thing, and I'm sure it will help me get to where I need to go. Thanks again. :-) |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return cursor to previous cell
Thanks, Art. I guess recording the macro and replacing the part of the code
that creates the absolute reference with your code is the way to go. I'll try this very soon and see if it works, but am under time pressure with something else at the mo, so I'll store it in a text file I keep for these things and come back to it just a little later. Many thanks. "Art" wrote: Kevryl, I'm not sure how to record this, but the following macro will place your range name "anchor" at the active cell: Sub test() Dim mRange As Name For Each mRange In ActiveWorkbook.Names If mRange.Name = "anchor" Then mRange.Delete Next ActiveWorkbook.Names.Add Name:="anchor", RefersTo:=ActiveCell End Sub "Kevryl" wrote: Hi Barb, Thanks for your interest. It could be any range, any range name, as long as when the macro is run, it creates the rangename relative to where the cursor is then, rather than to where it was when it was recorded. But OK, here is a typical simple example, similar to a function I have used in the past for continuously creating pay advices: 1) start recording the macro, 2) Go to the range "Anchor" (it matters not what the reference is because it will be different each time - see below step 10) 3) Select "relative" and select the range that includes 8 columns to the right and 6 rows down ([end][right and [end]down] are a tidy way of selecting these) 5) copy the contents to the clipboard 6) move cursor 8 rows down and paste 7) return to "Anchor". 8) Erase the rangename "Anchor" 9) Move cursor 8 rows down 10) Create range name "Anchor" 11) Stop recording Such a macro works perfectly in the old Lotus and Enable (a Lotus lookalike) because they record the keystrokes, rather than converting the results of the keystrokes to VB before recording. In Excel, the reverse is true and the "Relative" selection on the macro tool bar is ignored. To try and clarify the problem further: The first time the macro records the rangename "Anchor", if I'm on cell A1 then every time I run the macro it will return the cursor to cell A1 to recreate the name "Anchor", rather than moving the cursor 8 rows down to extend the form. There were so many of these commands that were very easy to use back in the 80's software mentioned. Another nice one was automated cursor movement with a "wait for input" option (so you could string together a whole automated sequence of data input anywhere on the worksheet). All these were on a convenient reference card that came with the software. These days you have to learn Visual Basic to do them. Anyway, I digress :-). The question is whether you can give me some simple macro code for creating a rangename relative to where the cursor is when the macro is run, rather than to where it was when it was recorded. Thanks again for your interest in my question. :-) Cheers "Barb Reinhardt" wrote: How about you show us the absolute reference you have and tell us what you want? "Kevryl" wrote: I'm using Excel 2000 It seems to be incapable of creating a relative range name during a macro. Even clicking the "relative" option still results in recording an absolute reference to the cell referenced during macro creation, rather than the keystrokes used to produce it so as to name the cell the cursor is in when the macro is executed. This inability has stymied so many little devices I've tried to implement and I'm really getting jack of the inability to do so many things I was doing with spreadsheets in Lotus 123 and Enable way back in the 80's! Obviously, I have to resort to Vbasic code. Can someone assist? I would be so grateful! Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to return to previous cell? | Excel Discussion (Misc queries) | |||
return cursor to previous cell? | Excel Discussion (Misc queries) | |||
Return cursor to previous position | Excel Discussion (Misc queries) | |||
Return Path to previous cell | Excel Discussion (Misc queries) | |||
Return to a previous cell | Excel Discussion (Misc queries) |