How to increment the Range of a For Each loop - Excel VBA

Question

I'm attempting to increase in a loop in which cells a formula is posted in. I have verified that my loop already functions; but, I am unsure of how to limit it so that it posts the formula every increment rather than every cell.

Perhaps this is not the place to use a For Each Loop?

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 juvenile attempt with:

rcell = rcell + 7

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

Answer 1

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  

 change the formula line to:

... = "=SUM(D" & I - 6 & ":D" & I - 1 & ")"

Then the output is: