Macro Finding First Empty Cell In Column Then Returning Date OR Time

[LechlerFan]

I have no experience editing macros. I can make two things work individually (sort of) but can't get them to work together.

What I want is to have a button that says, "Start." The associated macro finds the first empty cell in a particular column (let's say column B), selects it, and inserts today's date (using "Ctrl+;"). The same macro would then also find the first empty cell in column C and insert the current time, using "Ctrl+Shift+;".

A second button would say "Stop," with a macro that searches column D for the first empty cell and inserts the current time. I think I can figure out the "Stop" macro if I can get a grip on the "Start."

The result would look like the below, where the "Start" button fills columns B and C, and the "Stop" button fills column D. Bill has not yet clicked the "Stop" button on the 17th, but when he does, it will fill the empty cell in that column. The next time Bill clicks the "Start" button, B5 and C5 will fill with the current date and time.

[TABLE="width: 400"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Start Time[/TD]
[TD]Stop Time[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Bill[/TD]
[TD]03/10/2017[/TD]
[TD]09:15[/TD]
[TD]13:43[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Bill[/TD]
[TD]03/14/2017[/TD]
[TD]09:31[/TD]
[TD]15:30[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Bill[/TD]
[TD]03/17/2017[/TD]
[TD]09:20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Bill[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I hope this makes sense! Thanks!

 

[My Aswer Is This]

Try this:
Start script:

Sub Enter_Start()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(Lastrow, 2).Value = Date
Cells(Lastrow, 3).Value = Time
Application.ScreenUpdating = True
End Sub

Stop Script

Sub Enter_Stop()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(Lastrow, 4).Value = Time
Application.ScreenUpdating = True
End Sub

 

[Coding4Fun]

Your question seems very clear.

Try this, both macros are here, you just need to assign the shortcut you want to use by going into view -> macros -> options I am not sure if this can be assigned in the Code.

So just create a new macro, copy and paste the code below into the new macro, this will create 2 macros. Then edit each one with the shortcut stated above and then create buttons and assign the macros accordingly.

Sub Start_Button()

CurStartRow = Cells(Rows.Count, "B").End(xlUp).Row
CurStopRow = Cells(Rows.Count, "D").End(xlUp).Row


If CurStopRow < CurStartRow Then


    Cont = MsgBox("You have not stopped that previous row, are you sure you want to start a new one?", vbYesNo)
    
    If Cont = vbNo Then
    
        Exit Sub
        End If
    Else
    Range("B" & CurStartRow + 1).Value = Date
    Range("C" & CurStartRow + 1).Value = Time




End If
End Sub




Sub Stop_Button()


CurStopRow = Cells(Rows.Count, "D").End(xlUp).Row
CurStartRow = Cells(Rows.Count, "B").End(xlUp).Row


If CurStopRow = CurStartRow Then


    Cont = MsgBox("You have not started a row yet are you sure you want to stop?", vbYesNo)
    
    If Cont = vbNo Then
    
        Exit Sub
        End If
    Else


Range("D" & CurStopRow + 1).Value = Time


End Sub

 

[My Aswer Is This]

I think I made a mistake.
Try this:


Start script:

Sub Enter_Start()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(Lastrow, 2).Value = Date
Cells(Lastrow, 3).Value = Format(Time, "hh:mm")
Application.ScreenUpdating = True
End Sub

Stop script:

Sub Enter_Stop()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Cells(Lastrow, 4).Value = Format(Time, "hh:mm")
Application.ScreenUpdating = True
End Sub

 

[L. Howard]

Try this. Format the columns for the date and time formats you want.

Howard

Sub My_D_T_Start()
 Cells(Rows.Count, "B").End(xlUp)(2) = Date
 Cells(Rows.Count, "C").End(xlUp)(2) = Time
End Sub


Sub My_T_Stop()
 Cells(Rows.Count, "D").End(xlUp)(2) = Time
End Sub

 

[L. Howard]

Here is another way, run from one button on the sheet. You make choice for date/time in B & C or stop time in D.
Copy to a standard module. (Not the sheet module.)

Howard

Option Explicit

Sub my_Date_Time()

Dim myDT

myDT = Application.InputBox(prompt:="For DATE and START Time - enter " & """B""" & vbCr & vbCr & _
                                    "For Stop Time - enter " & """D""", _
                                     Title:="DATE n TIME 'R US", Type:=2)
     
With ActiveSheet
If myDT = " " Then Exit Sub
 
If myDT = "B" Or myDT = "b" Then
 Cells(Rows.Count, "B").End(xlUp)(2) = Date
 Cells(Rows.Count, "C").End(xlUp)(2) = Time

ElseIf myDT = "D" Or myDT = "d" Then
 Cells(Rows.Count, "D").End(xlUp)(2) = Time

 End If
End With
 
End Sub