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

Question

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.

Use a standard For loop : For x = 8 to LastRow Step 7 Then you would do Range("D" & x).Formula = "=SUM(D" & i & ":D" & c & ")" — Scott Craner
– Scott Craner, Commented Jan 6, 2023 at 17:53
As a side note, the way to select a cell offset from another cell is with .Offset(). for example, instead of rcell = rcell + 7, you would use Set rcell = rcell.Offset(7,0). This still wouldn't work inside your For Each Loop, but it's helpful to know. — Cameron Critchlow
– Cameron Critchlow, Commented Jan 6, 2023 at 18:21
@braX Would I need to set the range inside of the Loop? Otherwise I don't quite see how setting a range is necessary since the only thing interacting with it was the For Each rcell in rng portion. — Zrhoden
– Zrhoden, Commented Jan 6, 2023 at 18:58
The loop is based on the range, so you do not want to put it in the loop. That wouldn't make any sense. — braX
– braX, Commented Jan 6, 2023 at 19:03

Cameron Critchlow · Accepted Answer · 2023-01-06 18:18:53Z

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

This is current output:

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

edited Jan 6, 2023 at 18:18

answered Jan 6, 2023 at 18:08

Cameron Critchlow

1,8071 gold badge7 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Zrhoden Over a year ago

Oops, It definitely was supposed to be as you changed the formula to, I must've been going too quickly and set variables to the same integer.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related