Needs help with excel R1C1 notation fill series

[stuffy]

Hi guys,

I have a question in relation to R1C1 notation and fill series. I have a huge excel file that contains a huge amount of formulas, which the notation R1C1 is helping to reduce.
However, I have a list of formulas that are identical with the exception of the row number.
As an example, I have "=SUM(Data!R[255]C[3]:R[350]C[3])", then "=SUM(Data!R[8270]C[3]:R[8365]C[3]), then "=SUM(Data!R[16285]C[3]:R[16380]C[3])" and so forth. The difference between each of those formulas is the row number which is always 8015 more than the previous one. Now I could enter the formulas one by one, but that just not an option as it would take too me long. Any ideas on how I could speed up the process? I don't seem to have the option "fill series" as I suppose excel does not recognise that it is in fact a series (as only thing changing is the row number with 8015 more every single time). Maybe a macro (but I am not very good yet with macros)?

I would really appreciate some help with this.

Many Thanks!

 

[steve the fish]

I cant think of way to do that with autofill. Heres a way with a macro loop:

fr = 1 'first row for formula
col = 1 'column number to use

Do Until x > 100000 'change 100000 as appropriate
    Cells(fr, col).FormulaR1C1 = "=SUM(Data!R[" & 255 + x & "]C[3]:R[" & 350 + x & "]C[3])"
    fr = fr + 1
    x = x + 8014
Loop

 

[mikerickson]

In you could put this in A1 and drag down.

=SUM(INDEX(C[3]:C[3], ROW(RC1)*8015-7760, 1)+INDEX(C[3]:C[3], ROW(RC1)*8015-7760+95, 1))

If the starting row is other than A1, adjust the 7760

 

[stuffy]

Thanks guys for quick response. Unfortunately, the Macro loop is not doing anything at all, and the second suggestion doesn't take into account that the data the formula needs to look at is actually on a different sheet called "Data"

 

[mikerickson]

If you are dealing with off sheet references, just add the name of the sheet to the cell reference.

 

[steve the fish]

Im not sure how its possible the macro does nothing at all. Was it run?

 

[stuffy]

Hi Steve,
Apologies, but it looks like your solution might work. It appears to be working indeed. I just need to see as it stops after row 13.
Many thanks for your help!!!!

 

[stuffy]

Hi Steve...all good now. This is definitely quicker than entering cell by cell. Very good! Many thanks again!

 

[stuffy]

Hi Steve,

Was wondering if you could work your magic but for this formula
"=IFERROR(AVERAGE('1'!R[255]C[1]:R[350]C[1]),"")" - I keep trying to add it using the macro loop you provided below, but keep getting run time error. This is how I have done it:

Sub ForLoopAverage()
fr = 515 'first row for formula
col = 11 'column number to use


Do Until x > 248000 'change 100000 as appropriate
Cells(fr, col).FormulaR1C1 = "=IFERROR(Average('Data'!R[" & 255 + x & "]C[1]:R[" & 350 + x & "]C[1],"")"
fr = fr + 1
x = x + 8015
Loop
End Sub

Any ideas?