murgash

Hi guys,

Can somebody help me to modify the following code little bit?
This is what i worked out as code so far. The outstanding problem is:
to make the cell containing the "Average" to change for every "i" and be fixed(frozen) in Formula="If((and...........)"
For example, the R116C4 cell in the =If((and..)) formula must change for every successive step (see below code) but be frozen for every step.
Eg. in the next step for i=219, R116C4 should become R219C4 and so on.
Thanks in advance for any help.

Best regards,

Stefan

Sub Bob1()
Cells(13, 4).Resize(1, 1).FormulaR1C1 = "=AVERAGE(R[-11]C[-1]:RC[-1])"
Cells(14, 4).Resize(90, 1).FormulaR1C1 = "=IF((AND(RC[-1]>R13C4,RC[-1]>0)),1,0)"
Cells(13, 5).Resize(1, 91) = Application.Transpose(Cells(13, 4).Resize(91, 1))
For i = 116 To Cells(Cells.Rows.Count, 1).End(xlUp).Row Step 103
Cells(i, 4).Resize(1, 1).FormulaR1C1 = "=AVERAGE(R[-12]C[-1]:RC[-1])"
Cells(i + 1, 4).Resize(90, 1).FormulaR1C1 = "=IF((AND(RC[-1]>R116C4,RC[-1]>0)),1,0)"
Cells(i, 5).Resize(1, 91) = Application.Transpose(Cells(i, 4).Resize(91, 1))
Next
End Sub