Loop Formula Across Columns and Down Rows

[suremac]

Greetings,

I'd like to loop a formula across columns and have it stop when it gets to the last column that I have defined, while also looping the formula down rows to the last row that I have defined in the respective columns. I think I have a rough idea of how some things should be defined but not a good idea of the most efficient loop to use or how the formula should be formatted. It should be noted that, in the 12^th line of code, the starting row is variable. I’m open to using R1C1 notation if necessary to reformat the formula but I do not understand it very well.

Sub LoopAcrossColsRows()
Dim C as Integer
Dim R as Integer
Dim LastCol As Integer
Dim LastRow As Long
Dim lr As Long
Worksheets("Sheet1").Activate
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
LastRow  = Cells(Rows.Count, 20).End(xlUp).Row
lr = Cells(Rows.Count, 1).End(xlUp).Row
For C = 2 To Last Col
     For R = lr + 2 To LastRow
          Cells(C, R) = “=SUMPRODUCT(--(B6:B7>=$A20),--(B6:B7<=(A20+30))*B4)”
     Next R
Next C
End Sub

Any help is much appreciated.

 

[suremac]

Here is the formula with the references:

SUMPRODUCT(--(C$6:C$7>=$A20),--(C$6:C$7<=($A20+30))*C$4)

 

[suremac]

To whoever comes across this thread, this is how to loop a simple formula using R1C1 notation across columns and rows.

Sub LoopAcrossColsRows()
    Dim c As Long, lc As Long, r As Long, lr As Long
    With Sheets("Sheet3")
        lc = .Cells(1, Columns.Count).End(xlToLeft).Column
        lr = Cells(Rows.Count, 1).End(xlUp).Row
        For c = 3 To lc
            For r = 2 To lr
            .Cells(r, c).FormulaR1C1 = "=SUM(RC[-1])"
            Next r
        Next c
    End With
End Sub