Do while嵌套循环

Question

一个有四列的简单表，包含数据的column 1称为_Log Hour，包含数据的column 2称为Calls。每小时的Columns 3、4 _Hour logged和_Sum Calls当前为空，等待填充。

_Log Hour   Calls   _HourLogged _Sum Calls per Hour
8             3     
9             2     
9             4     
9             7     
9             2     
10            2     
10            2     
10            4     

我已经尝试了做while的基本方法

Sub automate()
    r = 2
    Do While Cells(r, 1) <> ""
    Cells(r, 3) = Cells(r, 1) * Cells(r, 2)
    r = r + 1
    Loop

End Sub

只是想看看它是如何工作的，这是成功的，但是它应该使用第一个_LogHour，在_Hourlogged中输入它，然后在_LogHour不变的情况下，我想要它求和调用。

例如，更新后的表将如下所示

_Log Hour   Calls   _HourLogged _Sum Calls per Hour
8             3         8                 3
9             2         9                15
9             4     
9             7     
9             2     
10            2         10                8
10            2     
10            4

Answer 1

这将是一种可用于此类型摘要的模式：

Sub automate()

    Const ROW_START As Long = 2
    Const COL_HR As Long = 1
    Const COL_CALLS As Long = 2
    Const COL_HR_SUMM As Long = 3
    Const COL_CALLS_SUMM As Long = 4

    Dim r As Long, rw As Range, currentHr, hr, rwHr As Long, sht As Worksheet

    Set sht = ActiveSheet
    r = ROW_START

    Do While sht.Cells(r, COL_HR).Value <> ""
        hr = sht.Cells(r, COL_HR).Value
        If r = ROW_START Or hr <> currentHr Then '<< at start, or a new hour value?
            rwHr = r                             '<< store the row we first saw this hour
            currentHr = hr                       '<< store the hour
            sht.Cells(rwHr, COL_HR_SUMM).Value = currentHr
            sht.Cells(rwHr, COL_CALLS_SUMM).Value = Cells(r, COL_CALLS).Value
        Else
            'not a new hour, so update the previously-saved row
            With sht.Cells(rwHr, COL_CALLS_SUMM)
                .Value = .Value + sht.Cells(r, COL_CALLS).Value
            End With
        End If

        r = r + 1

    Loop

End Sub