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 79 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:

• 53,160 points

## 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

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

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

## 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

## 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