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!