Naming the current cell in Excel

[Thomas]

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?

 

[Bob Phillips]

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)

 

[Guest]

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.

 

[Jan Karel Pieterse]

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

 

[Bob Phillips]

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)