Do while nested Loop

Question

A simple table with four columns, column 1 which has data is called _Log Hour, column 2 which has data, is called Calls. Columns 3 and 4 _Hour logged and _Sum Calls per Hour are currently empty waiting to be populated.

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

I've tried that basic of Do 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

Just to try and see how it works, that is a success but What it should take the first _LogHour, enter it in _Hourlogged then whilst the _LogHour doesn't change I want it sum Calls.

So, for instance, an updated table would look something like this

_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

Why not use formulae? SUMIF would do most of this for you.

SJR
– SJR

2019-07-29 15:15:08 +00:00
Commented Jul 29, 2019 at 15:15 — SJR
– SJR, Commented Jul 29, 2019 at 15:15
… or use a pivot table

AJD
– AJD

2019-07-29 20:41:20 +00:00
Commented Jul 29, 2019 at 20:41 — AJD
– AJD, Commented Jul 29, 2019 at 20:41

Tim Williams · Accepted Answer · 2019-07-29 16:42:12Z

1

This would be a pattern you can use for this type of summary:

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

edited Jul 29, 2019 at 16:42

answered Jul 29, 2019 at 16:36

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PJCAN1 Over a year ago

Tim - that works a treat and better still your commentary makes it clear to understand what it is doing, thanks very much

Collectives™ on Stack Overflow

Do while nested Loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related