0

After a few hours of searching I can not find any examples of how to perform this.

I am trying to create 2 buttons on my excel sheet for time tracking purposes (for easily logging my day to day activities). A clock-in button, and a clock-out button. The buttons are set up and work, with the proper script. Currently they just write the current time to the selected cell using the following: 

ActiveCell.Value = Time

This works fine, but I would like something a little more automated. What I would like each button to do is scan the column (clock-in for example is column C) from row 2 to the first blank cell, then insert the current time into that cell (So C2 would have Time). Next time the button is pressed, it will input Time into C3. Next press would input Time to C4, etc etc etc. That is the basic idea of what I am trying to do.

I am trying to do this in Excel 2007, if that matters
Bonus: How to do both clock-in/clock-out with one button. Not needed, but would be interesting to see.

Thanks,

Rich

pnuts
  • 58,317
  • 11
  • 87
  • 139
Richard Sanford
  • 11
  • 1
  • 2
  • If *time in* is in C where is *time out*? – L42 Oct 20 '14 at 23:57
  • 1
    Yo do not provide any code or effort to write your own code. Ok you have an idea how it should work, but that is not enough. This is not how SO works. To solve your problem check: http://stackoverflow.com/questions/71180/how-can-i-find-last-row-that-contains-data-in-the-excel-sheet-with-a-macro. Maybe the workbooks.open event is interesting for you too: http://msdn.microsoft.com/de-de/library/office/ff194819%28v=office.15%29.aspx if the code is not working, come back post the code and we will help you with it. – ruedi Oct 21 '14 at 01:09
  • 1
    This is super easy. So in your code find the last row in column Can. Google how to there 100s examples. Then cell.value = myvalue – causita Oct 21 '14 at 02:21
  • whats the purpose of clock in/out? Is it to track when file is open and when file is closed, right? And same question as L42 : where does clock out goes, col D or what? – ZAT Oct 21 '14 at 06:23
  • [THIS](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba) will get you started. – Siddharth Rout Oct 21 '14 at 07:38
  • *hours of searching...?* wow.... –  Oct 21 '14 at 07:42

2 Answers2

0
Sub clock_in()
    Cells(2, 3).Select ' starts at cell C2
    Do Until ActiveCell = ""
       ActiveCell.Offset(1, 0).Select
    Loop
    ActiveCell = Time
End Sub

How it works: This routine starts at cell C2 and works its way down the column looking for the first blank cell using a Do Loop statement. Once it finds a blank cell the do loop ends and the time is placed in the blank (active) cell.

blalasaadri
  • 5,990
  • 5
  • 38
  • 58
Mike Powell
  • 260
  • 1
  • 6
  • An explanation of what you're doing here would be helpful. – blalasaadri Oct 21 '14 at 12:38
  • This routine starts at cell C2 and works its way down the column looking for the first blank cell using a Do Loop statement. Once it finds a blank cell the do loop ends and the time is placed in the blank (active) cell. – Mike Powell Oct 21 '14 at 12:49
  • This also does the job great, and simple. I had originally been trying to do something along the lines of your other answer. The selection.end was throwing me off because I never use VBA. Thanks a lot – Richard Sanford Oct 21 '14 at 15:31
0

Another way:

Sub clock_in()
    Cells(2, 3).Select ' starts at cell C2
    If Cells(2, 3) = "" Then
        Cells(2, 3) = Time
        Exit Sub
    End If
    If Cells(3, 3) = "" Then
        Cells(3, 3) = Time
        Exit Sub
    End If
    Selection.End(xlDown).Offset(1, 0).Value = Time
End Sub

This routing checks to see if cell C2 is empty. If it is it places the time in cell C2. If it isn't empty, it checks cell C3 and places the time in that cell. But if C3 is not empty, it uses an end select statement to find the next blank cell. The reason it checks for cell C2 & C3 is that if you do an end select statement and cell C2 or C3 are blank it will go to the bottom of column C in your spreadsheet.

blalasaadri
  • 5,990
  • 5
  • 38
  • 58
Mike Powell
  • 260
  • 1
  • 6
  • This routing checks to see if cell C2 is empty ... if it is it places the time in cell C2. if it isn't empty, it checks cell C3 and places the time in that cell.. but if C3 is not empty, it uses an end select statement to find the next blank cell. The reason it checks for cell C2 & C3 is if you do an end select statement and cell C2 or C3 are blank ... it will go to the bottom of cloumn C in your spreadsheet. – Mike Powell Oct 21 '14 at 12:51
  • This does exactly what I want it to do. – Richard Sanford Oct 21 '14 at 15:12