CODEWITHSUNDEEP
vbaexcel
babsdoc
babsdoc

Reputation: 749

VBA Formula Referencing

I tried recording a formula in excel as a macro =MAX(Q2:Q3) which comes up in the VBA code as a R1C1 reference ActiveCell.FormulaR1C1 = "=MAX(RC[-1]:R[1]C[-1])"

Is there any way I can input the formula in the VBA editor as the Excel reference rather than the R1C1 type of reference?

Upvotes: 0

Views: 602

Answers (2)

avdhesh maurya
avdhesh maurya

Reputation: 115

Could you please try to disable the R1C1 reference style from setting. I am not sure which version you are using. I am using Mac OS, so for me it is something like Excel->Preferences->General->Use R1C1 reference style. Maybe I can check on some windows machine & confirm you, if it is same.

Regards, Avdhesh

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33672

The Excel MACRO recorder default setting is using the FormulaR1C1, just change to Formula and type the Range you need:

ActiveCell.Formula = "=MAX(Q2:Q3)"

In the future, if you want to use also the Range object, and allow yourself more flexibility, you can use something like the code below:

Dim Rng As Range

Set Rng = Range("Q2:Q3")
ActiveCell.Formula = "=MAX(" & Rng.Address(False, False, xlA1) & ")"

Upvotes: 3

Related Questions