VBA program to sum minutes per month

[cocopoo]

Hi I would like to write a program that sums up minutes per month and displays a pop up for each month's sum. How would I go about that? Thank you

Month	Minutes
Jan	17
Jan	39
Jan	36
Jan	65
Feb	52
Feb	40
Mar	100
Mar	98
Mar	53
Apr	23
Apr	27
Apr	99
May	20

 

[lrobbo314]

Assuming your data begins in cell A1.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Range:         Set r = Range("A2:B" & Range("A" & Rows.Count).End(xlUp).Row)
Dim Itm As String:      If Target.Column = 2 Then Itm = Target.Offset(, -1).Value Else Itm = Target.Value

If Not Intersect(Target, r) Is Nothing Then

    Dim AR() As Variant:    AR = r.Value2
    
    With CreateObject("Scripting.Dictionary")
        For i = 1 To UBound(AR)
            If Not .Exists(AR(i, 1)) Then
                .Add AR(i, 1), AR(i, 2)
            Else
                .Item(AR(i, 1)) = .Item(AR(i, 1)) + AR(i, 2)
            End If
        Next i
        
        MsgBox .Item(Itm)
    End With
    
End If
End Sub

 

[cocopoo]

thank you would there also be a simpler way to do it with a nested loop?

 

[lrobbo314]

Here's a no-loop way.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim LR As Long:     LR = Range("A" & Rows.Count).End(xlUp).Row
Dim r As Range:     Set r = Range("A2:B" & LR)

If Not Intersect(Target, r) Is Nothing Then
    Dim a As Range:     Set a = Range("A2:A" & LR)
    Dim b As Range:     Set b = Range("B2:B" & LR)
    
    MsgBox Evaluate(Replace(Replace("=LET(u,UNIQUE(@),s,SUMIF(@,u,^),c,CHOOSE({1,2},u,s),ca,INDEX(c,,1),cb,INDEX(c,,2),TEXTJOIN(CHAR(10),1,ca & "" - "" & cb))", "@", a.Address), "^", b.Address))
End If
End Sub