如何增加For Each循环的范围- Excel VBA

ht4b089n 于 2023-01-10 发布在其他

关注(0)|答案(1)|浏览(193)

我有一个循环，我试图增加公式被张贴在哪些单元格。我确认我的循环已经工作，我只是不太知道如何限制它张贴公式每增量，而不是每一个单元格。
也许我不应该在这里使用For Each循环？

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

我只是不太清楚如何增加范围。你可以看到我的juevenile尝试：

rcell = rcell + 7

当然，这会导致数据类型不匹配，因为它是一个范围，而不是一个整数。

excel

来源：https://stackoverflow.com/questions/75034375/how-to-increment-the-range-of-a-for-each-loop-excel-vba

1条答案

按热度按时间

n6lpvg4x1#

您的公式似乎对1个单元格求和，如果不正确，您需要调整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

这是当前输出：