Excel Macro, Time input button

Closed
nickrenwick - Jan 14, 2011 at 02:27 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jan 19, 2011 at 09:17 AM
Hello,

Excel Macro Help PLEASEE!!!?

Ideally i want a time in and a time out button, so that when workers sign in they click the timein button and when they leave click timeout.

I have this code in a macro assigned to a button in my spreadsheet:

Sub timeIn()
Range("C4").Value = time()
End Sub
--------------------------------(minus lines obv)

I want it so that each consecutive time the button is clicked it will fill out the cell below as opposed to overwriting the one the time has just been entered into.
Something like:

Sub timein()
If ("C4") = 0 Then
Range("C4").Value = time()
Else
??????????

End Sub


Any help would be much apreciated!

1 reply

rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 768
Jan 19, 2011 at 09:17 AM
presuming that time in goes to a blank row, you can locate the last use row and then increment that row by one

Here a function that can give you the last row

sSheetName : the sheet on which you want to find the last used row
myRange : optional, a range in which you want to find the last used cell

It will return 0, if nothing is found or else would return the row number of the last used row


Function getLastRow(sSheetName As String, Optional myRange As Range = Nothing) As Long
' get last use row on the sheet

   Dim Cell          As Range
   
   If (myRange Is Nothing) _
   Then
      Set Cell = Sheets(sSheetName).Cells.Find("*", Cells(1, 1), , , xlByRows, xlPrevious)
   Else
      Set Cell = Sheets(sSheetName).Range(myRange.Address).Find("*", Sheets(sSheetName).Range(myRange.Address).Cells(1, 1), , , xlByRows, xlPrevious)
   End If
   If Cell Is Nothing Then
      getLastRow = 0
   Else
      getLastRow = Cell.Row
   End If
   Set Cell = Nothing

End Function
0