Macro to delete and recreate range name (say) 10 rows down.

K

Kevryl

Working in Excel 2000.

I want to run a macro that deletes the name created on the previous running
of the macro, recreating the name (say) 10 rows down so that the data can
auotomatically be entered progressively down the sheet

I can't get the "Relative" button in the macro tool bar to work when
creating a range name within the macro. It always results in the range name
being absolute.

I have tried modifying the absolute statement (for example, reading)
"RefersToR1c1:= "=Sheet1!R356C5" to the Relative version,
"RefersToR1c1:= "=Sheet1!R[+2]C[+1]
However, the range name then will not attach to the reference (ie "stay
put") but to the position of the cursor (ie active cell) at the time the
macro is executed.

In all my years in the 80's and early 90's using "Enable" I never had a
difficulty in recording macro's as actual keystrokes, but Excel wants to
interpret those keystrokes in its own dictatorial way, recording the results
of the keystrokes rather than the simple act of them.

Has anyone solved this one?
 
B

Bernie Deitrick

Kevryl,

No need to use named ranges. The typical way to fill in the first open cell
at the bottom of column A is to use this (though you can't have any other
blank cells in column A):

Range("A1").End(xlDown)(2).Value = "Whatever"

HTH,
Bernie
MS Exel MVP
 
K

Kevryl

Bernie, thanks for taking the time :)

I am familiar with the end-down sequence and have used it for many years in
macros. I have tried to simplify my problem in my initial question, but to
give you more of a graphic idea of what I'm tryting to achieve, I'll tell you
about an application that I put "Enable" to many years ago now:

Imagine, if you will, an employee pay advice, set out on screen covering
probably 15 rows and 10 columns. It gives all the information, and cumulative
information as to hours worked, overtime, Gross pay, tax and nett, plus
allowances, superannuation etc etc etc.

At the top of the worksheet is a blank pay advice form. Each time the sheet
opens, it executes a macro that makes a copy of the blank pay advice below
the original for the next fortnight, complete with all formulae referring to
the above copy, to carry forward cumulatives and make necessary calculations.
It does this by going to a named range (the original, or last copy), copying
the blank below itself, then returning to the copied area to enter the data.
"Enable" actually had some nice functions for automating cursor movement and
pausing for input, too and you could include these in macro instructions
typed into a cell (no Vbasic knowledge needed, just a reference to the
"functions" quick reference card that came with the software.)

As the pay advice form is copied, the range name is deleted and recreated at
the new copy for the next time the sheet is opened. The recreation was always
to a relative reference, because it was the number of "down" keystrokes that
it recorded, rather than the results of the keystrokes. Lotus 123 actually
worked very similarly.

The ability to goto, delete and recreate a named range x rows down an ever
expanding worksheet or data range by macro is a very powerful function when
it can be done. In many ways Excel is a backward step on programs like 123
and "Enable" because some of the very powerful functions of these programs
(for example, the ability to create a branched macro that uses functions like
IF, MAX and MIN, to determine which sub-macro is executed) don't exist in
Excel, unless you are prepared to learn the VB programming language.

BFN
Regards

Kevryl said:
Working in Excel 2000.

I want to run a macro that deletes the name created on the previous running
of the macro, recreating the name (say) 10 rows down so that the data can
auotomatically be entered progressively down the sheet

I can't get the "Relative" button in the macro tool bar to work when
creating a range name within the macro. It always results in the range name
being absolute.

I have tried modifying the absolute statement (for example, reading)
"RefersToR1c1:= "=Sheet1!R356C5" to the Relative version,
"RefersToR1c1:= "=Sheet1!R[+2]C[+1]
However, the range name then will not attach to the reference (ie "stay
put") but to the position of the cursor (ie active cell) at the time the
macro is executed.

In all my years in the 80's and early 90's using "Enable" I never had a
difficulty in recording macro's as actual keystrokes, but Excel wants to
interpret those keystrokes in its own dictatorial way, recording the results
of the keystrokes rather than the simple act of them.

Has anyone solved this one?
Click to expand...
 
Top