Macro for recurring formula; max of set of cells

[ucanalways]

Hello Group,

I am trying to automate this using a macro. I am trying to populate
the values of set of cells with a single click of a command button.
I tried FOR loop and all I get is just errors. Could someone please
help me to write a macro for the following:

Case 1:
'Max of interval 18
Formula in cell D1 =max(E2:E19)
Formula in cell D2 =max(E20:E37)
Formula in cell D3 =max(E38:E55)
......
Formula in cell D117......

Case2:
'Max of interval 18 - Mid value of interval 18
Formula in cell G1 =(max(F2:F19) - F10)
Formula in cell G2 =max(F20:F37) - F28)
Formula in cell G3 =max(F38:F55) - F46)
......
Formula in cell D117.....

Any help would be much appreciated.

Thanks,
Kevin

 

[Mike H]

Try this,

Right click your sheet tab, view code and paste this in and run it. If you
want the step to be variable you could do that with an input box to get the
step value or read it from a worksheet cell.

Sub FillumUp()
myrow = 1
For X = 2 To 5000 Step 18
Range("D" & myrow).Formula = "=Max(E" & X & ":E" & X + 17 & ")"
Range("G" & myrow).Formula = "=Max(F" & X & ":g" & X + 17 & ")-F" & X + 8
myrow = myrow + 1
If myrow = 118 Then Exit Sub
Next
End Sub

Mike

 

[Mike H]

OOPS,

Typo. Try this instead

Sub FillumUp()
myrow = 1
For X = 2 To 5000 Step 18
Range("D" & myrow).Formula = "=Max(E" & X & ":E" & X + 17 & ")"
Range("G" & myrow).Formula = "=Max(F" & X & ":F" & X + 17 & ")-F" & X + 8
myrow = myrow + 1
If myrow = 118 Then Exit Sub
Next
End Sub