Using a macro to enter a formula

T

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
 
J

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
 
J

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
 
N

Niek Otten

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

or

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

Jacob Skaria

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

Niek Otten said:
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

Tami said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top