FormulaR1C1 and #REF!

[SBF12345]

I am receiving the #REF ! error in cells on a sheet generated when applying the following code into a vba sub.

When I view the formula in the sheet the #REF ! symbol is in the first R1C1 cell referenced (R[-23]C[-7]) and (R[-69]C[-7])

Range("L6").value = "dprice1"    Range("L7").FormulaR1C1 = "=((R[-23]C[-7])-(RC[-7]))/RC[-7]"
    Range("M6").value = "dprice2"
    Range("M7").FormulaR1C1 = "=((R[-69]C[-8])-(RC[-8]))/RC[-8]"

I am confused as to why the #REF ! error is being displayed. How are the #REF ! cells "invalid"?

 

[Dryver14]

You are in trying to go 23 rows up from row 7

 

[Fluff]

And in col M your trying to up 69 rows from row 7

 

[Jonmo1]

Take just the first one
Range("L7").FormulaR1C1 = "=((R[-23]C[-7])-(RC[-7]))/RC[-7]"

You're putting the formula in L7.
R[-23] is 'Offsetting' from the row you put the formula (7) by -23 rows, so it's trying to go 23 rows 'above' row 7, which doesn't exist.

What are you expecting the end result formula to look like in the cell ?

 

[SBF12345]

I am looking for a percentage value in the resulting cells in column "L".

Is it possible to return a value of "0" if the cells are "invalid" say for the first 16 cells in the case of the -23 and 62 in the case of the -69?

Oddly enough, every cell in column "L" is returning a #REF ! value even at row values of 2000+

 

[SBF12345]

What are you expecting the end result formula to look like in the cell ?

If possible I would like to receive errors or 0 values until all cells in the formula are within a range that picks up cells with integer values. In other words at row 24 I would like to have a numerical value show in the column with the formula using the -23 value.

 

[Jonmo1]

What I mean is, imagine you are not using VBA.
You're entering the formulas into the cells by hand.
What would those formulas be ?

 

[SBF12345]

I'm not sure I am following you here.

The formulas would find % changes in prices for differences between the current date and another date...23 and 69 days away from the current date. In A1 a formula might look like:

=(A5-A28)/A28

I am not sure how to handle the values that result from ranges that are outside the available spreadsheet given the current R1C1 structure.

I may be able to start from the bottom of the page and autofill up to the top. What do you think?