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

0 votes

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.

Jan 7 in Others by Kithuzzz
• 28,700 points
79 views

1 answer to this question.

0 votes

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:


enter image description here

answered Jan 7 by narikkadan
• 53,160 points

Related Questions In Others

0 votes
1 answer

How to apply zoom animation for each element of a list in angular?

Hey @Sid, do check if this link ...READ MORE

answered Jul 30, 2019 in Others by Vardhan
• 13,200 points
692 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 53,160 points
148 views
0 votes
1 answer

Excel VBA : HOW TO PRINT THE TEXT IN A CELL (like Wrap Text)

Use a LineFeed character to create a ...READ MORE

answered Oct 27, 2022 in Others by narikkadan
• 53,160 points
186 views
0 votes
1 answer

Creating a function in excel VBA to calculate the average point in a circular set of numbers

I used the following code to determine ...READ MORE

answered Oct 28, 2022 in Others by narikkadan
• 53,160 points
160 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
530 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,408 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,940 points
117 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,630 points
294 views
0 votes
1 answer

How to add Conditional Formatting in Excel for a Range of Values

Three distinct rules are required, one for ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 53,160 points
127 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 53,160 points
1,152 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP