VBA SUM变量范围 [英] VBA SUM Variable Range

问题描述

我想如果满足某些条件，则将可变行对齐的代码。
例如如果A12是数字，B12是空的，则在单元格C12中插入一个总和C3：C11。
然后在C22和C30执行相同的操作。
我遇到的问题是不知道如何定义起始行。

I want a code to sum the Variable rows up if certain condition is met. e.g. If A12 is numeric and B12 is empty then insert a fomula in cell C12 to sum C3:C11. Then perform the same action at C22 and C30. The problem I have is don't know how to define the starting row.

Sub Test()
Dim y As Variant
Dim r As Variant
Dim StartRow As Variant

   LastRow = Range("C" & Rows.Count).End(xlUp).Row
        For y = 3 To 500
            For r = 1 To LastRow

            If InStr(1, Cells(r, 1), "Amount") Then
                StartRow = r

            If IsNumeric(Cells(y, 1)) And IsEmpty(Cells(y, 2)) Then
            Cells(y, 3).Formula = "=SUM(C" & StartRow + 1 & ":C" & y - 1 & ")"
            End If
         End If
      Next r
  Next y

End Sub

推荐答案

Sub Test()
Dim y As Variant
Dim firstRow As Variant
Dim lastRow As Variant
lastRow = Range("C" & Rows.Count).End(xlUp).Row
firstRow = Cells(lastRow, 3).End(xlUp).Row
If IsNumeric(Cells(lastRow + 1, 1)) And IsEmpty(Cells(lastRow + 1, 2)) Then
    Cells(lastRow + 1, 3).Formula = "=SUM(C" & firstRow & ":C" & lastRow & ")"
End If
For y = firstRow To 3 Step -1
    lastRow = Cells(y, 3).End(xlUp).Row
    firstRow = Cells(lastRow, 3).End(xlUp).Row
    If firstRow < 3 Then firstRow = 3
    If IsNumeric(Cells(lastRow + 1, 1)) And IsEmpty(Cells(lastRow + 1, 2)) Then
        Cells(lastRow + 1, 3).Formula = "=SUM(C" & firstRow & ":C" & lastRow & ")"
    End If
    y = firstRow
    If firstRow = 3 Then Exit Sub
  Next y
End Sub

这篇关于VBA SUM变量范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！