Time tracker macro for Excel

Question

I bet the solution to this is easy but I can't just wrap my head around this. So basically this is how my table looks

start_time (A1)	stop_time (B1)

I want a macro that when it's clicked, it will go to cell A2 and put the current local time there. Clicked again, it goes to cell B2 and put the local time there. Clicked again, it goes to cell A3, then B3, A4, etc.

I think the logic is

Check cell Ax for blank:
- If yes, insert the time
- If no, move to cell Bx. Is it blank?
  - If yes, insert the time
  - if no, insert the time in cell A(x+1)

I just don't know how to actually put this in code. Thanks in advance for your help!

`IsEmpty` can check if a cell is blank. Also see [how to find the last used cell](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). — BigBen, Apr 06 '21 at 16:44
I know how to check it but I don't know how to automatically select the next row if the row being checked doesn't have any blank value anymore — Long N, Apr 06 '21 at 16:53
The last row (as long as it's greater than one) will *never* be blank. — BigBen, Apr 06 '21 at 16:55
Apologies but since I'm not a super VB user, could you please help providing the code? — Long N, Apr 06 '21 at 17:04

score 0 · Answer 1 · answered Apr 06 '21 at 17:17

0

We check the number of cells used. If Ax is greater than Bx, then we write the value in Bx. If not, we set it on Ax.

Option Explicit
Public Sub SetTime()

    Dim lastARow As Integer
    Dim lastBRow As Integer
    
    lastARow = Cells(Rows.Count, 1).End(xlUp).Row
    lastBRow = Cells(Rows.Count, 2).End(xlUp).Row
    
    If lastARow > lastBRow Then
        Cells(lastBRow + 1, 2) = Now
    Else
        Cells(lastARow + 1, 1) = Now
    End If

End Sub

answered Apr 06 '21 at 17:17

aklassen

45
4

This didn't work for me but thanks though – Long N Apr 06 '21 at 17:33
Just tried it and it works. Can you elaborate on what part is not working? Do you get any errors? – aklassen Apr 06 '21 at 17:34
@aklassen maybe because you're not explicitly referencing the sheet? – Nicholas Hunter Apr 06 '21 at 17:36
@aklassen: My true data is not in the exact cell like my example. Start _time was actually at cell G17 and stop_time at H17. SO i changed your macro Option Explicit Public Sub SetTime() Dim lastARow As Integer Dim lastBRow As Integer lastARow = Cells(Rows.Count, 7).End(xlUp).Row lastBRow = Cells(Rows.Count, 8).End(xlUp).Row If lastARow > lastBRow Then Cells(lastBRow + 1, 8) = Now Else Cells(lastARow + 1, 7) = Now End If End Sub And they just keep inserting the time in the start_time column only – Long N Apr 07 '21 at 18:54

score 0 · Accepted Answer · answered Apr 06 '21 at 17:23

Option Explicit

Public Sub TimeStamp()

    Dim row As Long
    Dim col As Long
    
    With Worksheets("Sheet1")
        If IsEmpty(.Cells(1, 1)) Then
            row = 1
            col = 1
        Else
            row = .Columns(1).Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
            If IsEmpty(.Cells(row, 2)) Then
                col = 2
            Else
                row = row + 1
                col = 1
            End If
        End If
        .Cells(row, col).Value = Now
    End With
    
End Sub

Thanks Nicholas for saving my life. This works perfectly! – Long N Apr 06 '21 at 17:34 — Long N, Apr 06 '21 at 17:34

Time tracker macro for Excel

2 Answers2