M
Marss
How can I use that "Relative Reference" when I'm recording a macro in Excel
2003?
2003?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Gord Dibben
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 Tools>Customize>Toolbars. 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
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 Tools>Customize>Toolbars. 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
M
Marss
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.
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.
G
Gord Dibben
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
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
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 said: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 Tools>Customize>Toolbars. 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