Dynamic Formula Ranges with reference to values in another cell

**dunda1985** · 06-16-2011, 07:07 PM

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!

**rylo** · 06-16-2011, 07:10 PM

Hi

try

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

rylo

**TMShucks** · 06-16-2011, 07:11 PM

Try:

TheOffsetValue = Range("D1").Value

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

Regards

**dunda1985** · 06-17-2011, 04:31 AM

Thanks guys, much appreciated. I had that formula originally but forgot to put in the spaces and dismissed it...

**TMShucks** · 06-17-2011, 05:08 AM

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