I want to know the code

Report
-
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
-
Hello,

this is my code i try update the data automatically but the macro is not working of the updating part
datetime auto_cycle tool time_difference

Sub MyMacro()
    Dim Total As Double
    Dim Timein As Date
    Dim Timeout As Date
    Dim a As Integer
    Dim b As Integer
    a = 2
    b = 0
    For a = 2 To 21
    b = a + 1
    If a >= 22 Then
        a = 2
    Else
        Timein = CDate(Cells(a, 1).Value)
        Timeout = CDate(Cells(b, 1).Value)
        Total = TimeValue(Timeout) - TimeValue(Timein)
        Debug.Print Total
        Debug.Print Format(Total, "hh:mm:ss")
        Cells(a, 4).NumberFormat = "hh:mm:ss"
        Cells(a, 4).Value = Total
        Debug.Print "'Number of hours = '" & Total * 24
     End If
   Next a
   
      
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$A$1" Then
        Call MyMacro
    End If
     
End Sub


this is my master file suppose i change the value in A COLUMN corresponding data should updated automatically without trigger the macro assigned button how to do this

01-01-2021 :10:10:10 1 1 00:00:10
03-01-2021 :10:10:20 1 2 00:00:02
13-10-2021 :10:10:22 1 3 00:00:04
13-10-2021 :10:10:26 1 4 00:00:04
13-10-2021 :10:10:30 1 5 00:00:06
13-10-2021 :10:10:36 1 6 00:00:04
13-10-2021 :10:10:40 1 7 00:00:03
13-10-2021 :10:10:43 1 8 00:00:00
13-10-2021 :10:10:43 1 9 00:00:06
13-10-2021 :10:10:49 1 10 00:00:03
13-10-2021 :10:10:52 1 11 00:00:08
13-10-2021 :10:11:00 1 13 00:00:10
13-10-2021 :10:11:10 1 12 00:00:04
13-10-2021 :10:11:14 1 14 00:00:05
13-10-2021 :10:11:19 1 16 00:00:04
13-10-2021 :10:11:23 1 17 00:00:04
13-10-2021 :10:11:27 1 18 00:00:02
13-10-2021 :10:11:29 1 19 00:00:04
13-10-2021 :10:11:33 1 20 00:00:05
13-10-2021 :10:11:38 1 21 00:00:07
13-10-2021 :10:11:45 1 12

1 reply

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
Hi Vathsala,

Not sure how your sheet is made up, but when you have times like "hh:mm:ss" in column A and you want the time difference in column D, then you can give the code below a try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("A")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub

With Target.Offset(0, 3)
    .NumberFormat = "hh:mm:ss"
    .Value = Target.Offset(1, 0).Value - Target.Value
    If .Row = 2 Then Exit Sub
    If .Value < 0 Then .Value = vbNullString
End With

With Target.Offset(-1, 3)
    .NumberFormat = "hh:mm:ss"
    .Value = Target.Value - Target.Offset(-1, 0).Value
End With
End Sub


Best regards,
Trowa