Add a value to current cell using FormulaR1C1

I need to add value in cell Z7 to the value in J2 (and retain the value in J2).

This does not work (adding another cell to same cell)

                        Cells(2, 10).FormulaR1C1 = "=RC+R[5]C[16]"

But, this works. (Adding two different cells - Y7 and Z7 - and storing the sum in J2)

                        Cells(2, 10).FormulaR1C1 = "=R[5]C[15]+R[5]C[16]"

What do I need to do to make the first command work?

tia
ajay

Hi Ajay

perhaps something using similar logic

Sub Replace()

Dim newv As Long
Dim oldv As Long
oldv = ActiveSheet.Range("J2").Value

newv = oldv + Range("Z7")
Range("J2").Value = newv

End Sub

Hi, ajaykgarg,

what you try to do looks like creating a circular reference to a cell where you want to insert a formula - you could place the current value of the cell and add the address of new one to the formula instead if you really want a formula in the cell.

Ciao,
Holger

Originally Posted by HaHoBe

Hi, ajaykgarg,

what you try to do looks like creating a circular reference to a cell where you want to insert a formula - you could place the current value of the cell and add the address of new one to the formula instead if you really want a formula in the cell.

Ciao,
Holger

Hi Holger

You are right. I made it work without a formula, but I need the formula, so users can change values in future, and see the updated results.

I need to do this for thousands of values.
I can maybe use a hidden sheet to do it for every value.

I will go for that option, if I cant find any other simpler way of doing it.

Thanks
Ajay

Hi, Ajay,

With Cells(2, 10)
  .FormulaR1C1 = "=" & .Value & "+R[5]C[16]"
End With

Ciao,
Holger