Recording a macro ...
 

How can I use that "Relative Reference" when I'm recording a macro in Excel
2003?

Just click it on to record relative references rather than hard-coded cell
addresses.

It will record code such as ActiveCell.Offset(5, 0).Select

rather than Range("A8").Select

And if you see the Stop Recording Toolbar but no Relative Reference button,
you can "reset" the Toolbar under ToolsCustomizeToolbars. You may have
dragged the button off.

The usual cause of losing the entire Stop Recording Toolbar is turning it off
when recording by using the "X" to close. Always hit the "stop recording"
button.


Gord Dibben Excel MVP

On Wed, 16 Feb 2005 11:49:05 -0800, "Marss"
wrote:

How can I use that "Relative Reference" when I'm recording a macro in Excel
2003?

Thank you. My problem is that I have a formula and my code looks like

ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"

with or without "Relative Reference". Is this OK for cells with formulas?
Yes, for cells without formulas the code looks like you told me.

Marss.

"Gord Dibben" wrote:

Just click it on to record relative references rather than hard-coded cell
addresses.

It will record code such as ActiveCell.Offset(5, 0).Select

rather than Range("A8").Select

And if you see the Stop Recording Toolbar but no Relative Reference button,
you can "reset" the Toolbar under ToolsCustomizeToolbars. You may have
dragged the button off.

The usual cause of losing the entire Stop Recording Toolbar is turning it off
when recording by using the "X" to close. Always hit the "stop recording"
button.


Gord Dibben Excel MVP

On Wed, 16 Feb 2005 11:49:05 -0800, "Marss"
wrote:

How can I use that "Relative Reference" when I'm recording a macro in Excel
2003?

Correct.

That R1C1 code is always relative referenced from the activecell.

Formula references cells 3 rows up to 1 row up from the cell you entered the
formula into.

With RR button on......

ActiveCell.Offset(3, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=R[-14]C[-9]+R[-13]C[-9]"
ActiveCell.Offset(1, 0).Range("A1").Select

With RR button off......

Range("J12").Select
ActiveCell.FormulaR1C1 = "=R[-14]C[-9]+R[-13]C[-9]"
Range("J13").Select

Note the R1C1 reference doesn't change.


Gord

On Wed, 16 Feb 2005 18:47:02 -0800, "Marss"
wrote:

Thank you. My problem is that I have a formula and my code looks like

ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"

with or without "Relative Reference". Is this OK for cells with formulas?
Yes, for cells without formulas the code looks like you told me.

Marss.

"Gord Dibben" wrote:

Just click it on to record relative references rather than hard-coded cell
addresses.

It will record code such as ActiveCell.Offset(5, 0).Select

rather than Range("A8").Select

And if you see the Stop Recording Toolbar but no Relative Reference button,
you can "reset" the Toolbar under ToolsCustomizeToolbars. You may have
dragged the button off.

The usual cause of losing the entire Stop Recording Toolbar is turning it off
when recording by using the "X" to close. Always hit the "stop recording"
button.


Gord Dibben Excel MVP

On Wed, 16 Feb 2005 11:49:05 -0800, "Marss"
wrote:

How can I use that "Relative Reference" when I'm recording a macro in Excel
2003?