Adding 2 times together when present and then update sheet

[stirlingmw]

Morning All

I hope the Thread title sort of explains what I am trying to do.

I am using the following code to return the sum of times from Column K in the corresponding cell in Column L (Basically a running total as times are added). The code works fine to add the total time into Column L, but if I change any of the times in the range "K10 to K29" I need the running total in Column L to recalculate.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("K11:K29")) Is Nothing Then
    If Target <> "" Then
    Target.Offset(0, 1) = WorksheetFunction.Sum(Range(Range("K10", Target.Address))
    End If
End If

End Sub

Any Ideas?

Thanks Steve

 

[Lee J]

Hi Stirlingmw,

I believe the above is calculating the value for you and then inserting this into the cell, which then would not recalculate as its a value not a formula, so would the below work for you:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim CurrentRow As String
CurrentRow = ActiveCell.Row

If Not Intersect(Target, Range("K11:K29")) Is Nothing Then

    If Target <> "" Then Target.Offset(0, 1).Activate
ActiveCell.Formula = "=SUM($K$10:$K$" & CurrentRow & ")"

End If

End Sub

This inserts the actual formula in to Column L on the current line instead of the value so far and as new lines are added keeps that formula going. so if you change K15 from 01:00 to 02:00 L15 would increase by 01:00 and so would L16, L17, L18 and so on but not L14 as its before K15.

 

[Lee J]

Meant to add that you might want to include this also to format the time if you go above 24:00 as currently it will return 00:00...

ActiveCell.NumberFormatLocal = "[h]:mm"

after the other ActiveCell command

 

[stirlingmw]

Lee

Fantastic thank you. I have altered your code slightly and added -1 to the CurrentRow in the formula. Works a treat now. Thank you