Using a macro to enter a formula

[Tami]

i'm trying to writ a macro that enters in a formula in a cell.

Scenario:
Assume my cursor is in row 8
I want to put a formula of "=O$9" in cell M9

Here's what i've got so far (but its not working)

dim x as integer
lr=activecell.row 'tells me what row i'm on
x=lr+1 'adds 1 to row number
cells(x,13).select 'move my cursor to column M of row x
ActiveCell.FormulaR1C1 = "=+RxC[2]" 'put the O$x formula in

thanks in advance for any assistance.
tami

 

[Jacob Skaria]

Hi Tami

Try the below

Dim x As Integer
lr = ActiveCell.Row 'tells me what row i'm on
x = lr + 1 'adds 1 to row number
Cells(x, 13).Formula = "=O$9" 'put the O$x formula in

If this post helps click Yes

 

[Jacob Skaria]

Oops...correction

Dim x As Integer
lr = ActiveCell.Row 'tells me what row i'm on
x = lr + 1 'adds 1 to row number
Cells(x, 13).Formula = "=O$" & x 'put the O$x formula in

If this post helps click Yes

 

[Niek Otten]

ActiveCell.FormulaR1C1 = "=R" & x & "C[2]"

or

ActiveCell.FormulaR1C1 = "=RC[2]", which means "this row", 2 columns to the
right

 

[Jacob Skaria]

Thanks Niek. I realise; I missed the query all together..

ActiveCell.FormulaR1C1 = "=R" & x & "C[2]"

or

ActiveCell.FormulaR1C1 = "=RC[2]", which means "this row", 2 columns to the
right

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

i'm trying to writ a macro that enters in a formula in a cell.

Scenario:
Assume my cursor is in row 8
I want to put a formula of "=O$9" in cell M9

Here's what i've got so far (but its not working)

dim x as integer
lr=activecell.row 'tells me what row i'm on
x=lr+1 'adds 1 to row number
cells(x,13).select 'move my cursor to column M of row x
ActiveCell.FormulaR1C1 = "=+RxC[2]" 'put the O$x formula in

thanks in advance for any assistance.
tami