macro records relative ref I want absolute ref.

[bubbafish]

Sub test()
'
'
' Record macro gives me this:
ActiveCell.FormulaR1C1 = "='Sheet1'!R[-45]C[8]"

' I want the actual cell it is getting...like this...but this doesn't work.

ActiveCell.Formula = "='Sheet1'!I3" ' This is what I want but it doesn't work ?

End Sub

 

[Jonmo1]

ActiveCell.Formula = "='Sheet1'!I3" ' This is what I want but it doesn't work ?

In what way does that not work? Do you get an error?
Works fine for me.

You said you want it absolute, so maybe that should be
ActiveCell.Formula = "='Sheet1'!$I$3"

 

[mrshl9898]

A couple of options, though the original works for me also

ActiveCell.Formula = Sheets("Sheet1").Range("I3").Value

ActiveCell.Formula = "='Sheet1'!R3C9"

 

[pjmorris]

Hi Jonmo,

I think the OP is referring to the results of recording a macro. ie, start macro recording, then type in a cell something like =Sheet1!B2, press enter, stop recording. The recorded macro then has ActiveCell.FormulaR1C1 = "=Sheet2!R[1]C[1]",ie the recording uses the R[]C[] style of addresses rather than the more usual.

I don't know of a way of changing this behaviour though?

Regards

Peter

 

[Jonmo1]

I don't know any way to change that either.
the Macro Recorder should be used as a guide to get syntax. It should not be depended on as a final resulting code.
We pretty much have to convert formulas it creates from R1C1 to A1 by hand.

 

[pjmorris]

Hi Jonmo,

Thanks for your concurrence - that's what I've been doing.

Regards

 

[bubbafish]

Thanks Everyone. MRSHL9898 both your examples worked for me. I had the wrong syntax.
Also on part 2 on my question...In the old days of previous versions of Excel when I did a record macro it would give me the optioin of absolute vs realitive references when I did a record. the result would be the actual cell like "I3" not something like R[-45]C[8]"
All questions answered though .
thanks again!!!

 

[Jonmo1]

I don't recall that behavior.

The 'Use Relative References' option in the macro recorder isn't about formulas created by that macro.
It's about how the Active Cell is moved around.

With Absolute referencing, recording a macro moving your cursor from say A1 to D12 would result in
Range("D12").Select

With Relative refernceing, that same action would result in
ActiveCell.Offset(11, 3).Range("A1").Select

But formulas entered using the macro recorder are still enteredin R1C1 style regardless of that option chosen or not.