I am converting macros from 123 to Excel. The macro needs
to be able to define a range name for the active cell.
Since the active cell can be on any row or in any column
when the macro is called, a fixed reference is not
acceptable. The recorded macro gives me this:
ActiveWorkbook.Names.Add Name:="here",
RefersToR1C1:="Bills!R29C1"
How can I modify this to be the current active cell?

Thomas,

Try this

ActiveWorkbook.Names.Add Name:="here", RefersToR1C1:="=Bills!RC"

But I have to ask, why bother, why not just use the cell address in the
formula?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Thomas" wrote in message
...
I am converting macros from 123 to Excel. The macro needs
to be able to define a range name for the active cell.
Since the active cell can be on any row or in any column
when the macro is called, a fixed reference is not
acceptable. The recorded macro gives me this:
ActiveWorkbook.Names.Add Name:="here",
RefersToR1C1:="Bills!R29C1"
How can I modify this to be the current active cell?

The complete macro is used in a sheet that lists the bills
to be paid. When activated the macro identifies the data
from the current row (Acct Name, Applicable checkbook, and
amount to be paid. It uses this info to complete the
checkbook entry, changes the date of the current line to
the schedule date for the next month, and resorts the list
of bills showing them in the anticipated order of payment.
Since the cursor can be on any bill and therefore on any
row of the bill schedule, the macro must be able to name
the active cell.

I haven't seen an example of your solution, but I'm
guessing that using RC without the number digits does this.

-----Original Message-----
Thomas,

Try this

ActiveWorkbook.Names.Add Name:="here",
RefersToR1C1:="=Bills!RC"

But I have to ask, why bother, why not just use the cell
address in the
formula?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Thomas" wrote in
message
...
I am converting macros from 123 to Excel. The macro
needs
to be able to define a range name for the active cell.
Since the active cell can be on any row or in any column
when the macro is called, a fixed reference is not
acceptable. The recorded macro gives me this:
ActiveWorkbook.Names.Add Name:="here",
RefersToR1C1:="Bills!R29C1"
How can I modify this to be the current active cell?


.

Thomas,

I would have thought that the macro could refer to Activecell more easily
that keep adding a name and then referring to that.

RC without digits picks up the activecell.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

wrote in message
...
The complete macro is used in a sheet that lists the bills
to be paid. When activated the macro identifies the data
from the current row (Acct Name, Applicable checkbook, and
amount to be paid. It uses this info to complete the
checkbook entry, changes the date of the current line to
the schedule date for the next month, and resorts the list
of bills showing them in the anticipated order of payment.
Since the cursor can be on any bill and therefore on any
row of the bill schedule, the macro must be able to name
the active cell.

I haven't seen an example of your solution, but I'm
guessing that using RC without the number digits does this.

-----Original Message-----
Thomas,

Try this

ActiveWorkbook.Names.Add Name:="here",
RefersToR1C1:="=Bills!RC"

But I have to ask, why bother, why not just use the cell
address in the
formula?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Thomas" wrote in
message
...
I am converting macros from 123 to Excel. The macro
needs
to be able to define a range name for the active cell.
Since the active cell can be on any row or in any column
when the macro is called, a fixed reference is not
acceptable. The recorded macro gives me this:
ActiveWorkbook.Names.Add Name:="here",
RefersToR1C1:="Bills!R29C1"
How can I modify this to be the current active cell?


.

Hi Thomas,

I am converting macros from 123 to Excel. The macro needs
to be able to define a range name for the active cell.


Activecell.Name="Test"

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com