Dynamic Formula Ranges with reference to values in another cell

Hi guys,

I think I posted this thread in the wrong forum originally. Was wondering if anyone could help me please:

This should be simple to solve but I can't get my head around it. I'm using a dynamic formula to calculate formulas, however I need the dynamic reference points to equal a certain value in a certain cell. The follow code should make the problem pretty apparent:

TheOffsetValue = Range("D1").Value

Range("D10").Activate
ActiveCell.FormulaR1C1 = "=((RC[-2]-R[-TheOffsetValue]C[-2])/R[-TheOffsetValue]C[-2])"

So for example if the value in cell "D1" is 10, then the formula will effectively use:

ActiveCell.FormulaR1C1 = "=((RC[-2]-R[-10]C[-2])/R[-10]C[-2])"

Thanks in advance for your help!

Try:

TheOffsetValue = Range("D1").Value

Range("D10").Activate
ActiveCell.FormulaR1C1 = "=((RC[-2]-R[-" & TheOffsetValue & "]C[-2])/R[-" & TheOffsetValue & "]C[-2])"

Regards

You're welcome. Thanks for the rep.

If this has answered your question, please mark your thread as solved. See my signature for details or the FAQ.

Regards