Excel Macro recording elapsed time - Help please!!

[Chrisjd2]

Hi All,

I am fairly new to excel VBA, I need a macro that helps log downtime on a system.

I propose to do this via a button that starts and stops a timer, in reality the first click - takes a time stamp, and so doesnt the second. Then you minus the two, giving you the allotted time.

However, because I need it to accumulate per day (on 1 row) I cannot get it to work like I want it to.

Could someone have a look please, I have posted the code and I will show you how the spreadsheet looks like.

Regards
Chris

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Day[/TD]
[TD]Save Time[/TD]
[TD]View Time[/TD]
[TD]Lost Time[/TD]
[TD]Total (hrs)[/TD]
[TD]Total (dec)[/TD]
[TD]Job No[/TD]
[TD]Comments[/TD]
[TD]H (column)[/TD]
[TD]I (column)[/TD]
[TD]J (column)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sub DownTime(xTime As Integer)

Dim COUNTER As Integer
Dim wsdowntime As Worksheet
Dim StartTime As String
Dim EndTime As String


Set wsdowntime = Worksheets("Downtime")




COUNTER = 2


While wsdowntime.Cells(COUNTER, 1) <> ""
COUNTER = COUNTER + 1
Wend


cEnd = COUNTER


COUNTER = 3




While wsdowntime.Cells(COUNTER, 1) <> ""


    If wsdowntime.Cells(COUNTER, 1) = Date Then
        If wsdowntime.Cells(COUNTER, 9) <> "" Then
            If wsdowntime.Cells(COUNTER, 2) = "" Then
                wsdowntime.Cells(COUNTER, 10) = Time
                wsdowntime.Cells(COUNTER, xTime) = (wsdowntime.Cells(COUNTER, 10) - wsdowntime.Cells(COUNTER, 9)) + wsdowntime.Cells(COUNTER, xTime)
            End If
        End If
    End If


    If wsdowntime.Cells(COUNTER, 1) = Date Then
        If wsdowntime.Cells(COUNTER, 9) = "" Then
            If wsdowntime.Cells(COUNTER, 2) <> "" Then
                wsdowntime.Cells(COUNTER, 9) = Time
            End If
        End If
    End If


    If wsdowntime.Cells(COUNTER, 1) = Date Then
        If wsdowntime.Cells(COUNTER, 9) <> "" Then
            If wsdowntime.Cells(COUNTER, 2) <> "" Then
                wsdowntime.Cells(COUNTER, 10) = Time
                wsdowntime.Cells(COUNTER, xTime) = (wsdowntime.Cells(COUNTER, 10) - wsdowntime.Cells(COUNTER, 9)) + wsdowntime.Cells(COUNTER, xTime)
                clear9 = 1
                clear10 = 1
            End If
        End If
    End If
 


COUNTER = COUNTER + 1


Wend






COUNTER = 3
While COUNTER <= cEnd


If wsdowntime.Cells(COUNTER, 1) = Date Then
    newline = 1
End If


COUNTER = COUNTER + 1
Wend


If newline = "" Then
    wsdowntime.Cells(COUNTER - 1, 1) = Date
    wsdowntime.Cells(COUNTER - 1, 9) = Time
End If


End Sub


Sub cSaving()
    Call DownTime(2)
End Sub


Sub cViews()
    Call DownTime(3)
End Sub


Sub cLostWork()
    Call DownTime(4)
End Sub