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 300 views

## 1 answer to this question.

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:

• 59,740 points

## Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

## How to automatically get a specified range of data from Excel to XML in VBA

Range method works, always identify the sheet ...READ MORE

## Retrieve epay.info Balance with VBA and Excel

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

## How to load file to Excel Power query from SFTP site

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

## Using VBA Excel to create a gramatically correct list

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