Return cursor to previous cell

[Kevryl]

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!

 

[Barb Reinhardt]

How about you show us the absolute reference you have and tell us what you
want?

 

[Kevryl]

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

 

[Art]

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

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

How about you show us the absolute reference you have and tell us what you
want?

 

[Norman Jones]

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

 

[Kevryl]

"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.

 

[Kevryl]

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.

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

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

How about you show us the absolute reference you have and tell us what you
want?

:

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!