How to increment the Range of a For Each loop

I have a loop where I am trying to increment which cells a formula gets posted in. I confirmed my loop already works, I just don't quite know how to limit it to post the formula every increment, instead of every cell.

Maybe I shouldn't be using a For Each Loop here?

Option Explicit

Sub Insert_Formula_Sum()

    Dim LastRow As Long, i As Long, c As Long
    Dim rng As Range, rcell As Range
    Dim LI As Worksheet
    Set LI = Sheets("Lumber Inventory")
    
    'Set lower range of dataset
    LastRow = LI.Range("A" & Rows.Count).End(xlUp).Row
    
    'Set range of For Each loop
    Set rng = Range("D8:D" And LastRow)
    
    i = 3
    c = 3
    
    For Each rcell In rng
        rcell.Formula = "=SUM(D" & i & ":D" & c & ")"
        i = i   7
        c = c   7
        rcell = rcell   7
    Next rcell

End Sub

I just don't quite know how I would go about incrementing the Range. You can see my juevenile attempt with:

rcell = rcell   7

But of course this gives a datatype mismatch, as this is dimmed as a Range and not an integer.

CodePudding user response：

Your formula seems to sum 1 single cell, if that's incorrect you'll need to adjust the - on I Start, Step, I-3, I-3.

Option Explicit

Sub Insert_Formula_Sum()

    Dim LastRow As Long
    Dim I As Long
    Dim LI As Worksheet
    
    Set LI = Sheets("Lumber Inventory")
    
    With LI
        
        ' Find Last Row#
        LastRow = .Range("A" & Rows.Count).End(xlUp).Row
        
        For I = 8 To LastRow Step 7
            .Range("D" & I).Formula = _
                "=SUM(D" & I - 3 & ":D" & I - 3 & ")"
        Next I
        
    End With

End Sub

This is current output:

But if you change the formula line to:
... = "=SUM(D" & I - 6 & ":D" & I - 1 & ")"
Then output is: