Reference help needed -> R[???]C[1]

[andy-s]

Hi

In the sheet named Data, cell A1 contains the formula =count(B:B)+1

I'm trying to write a code that change the reference of cell A1 in the sheet named Result. The refernce should be =C???, where ??? is the result from A1 from Data.

My code look like:

Sub Reference()
Sheets("Result").Select
ActiveCell.FormulaR1C1 = "='Data'!R[ ???? ]C[1]"
End Sub

Could anyone help me?

 

[erik.van.geit]

Hello, andy-s,

try this
ActiveCell.FormulaR1C1 = "='Data'!R[" & Sheets("Data").Range("A1") & "]C[1]"

if possible avoid to selector activate cells
like this
Range("A1") = 1234
or
Sheets("Results").Range("B5").FormulaR1C1 = "='Data'!R[" & Sheets("Data").Range("A1") & "]C[1]"

kind regards,
Erik

 

[andy-s]

Thx a lot