VBA - Create Punch Time

[poupinhas]

Hi everyone. I'm new to the website and also new to using VBA in Excel.
I'm trying to create a punch time table for workers to clock in and out.
I want do add a button that each click inserts the current time in the first empty cell it encounters from left to right and from top to bottom
It starts at C13 and ends at F43
It should insert clock in time in C13 than clock out in D13 than clock in again at E13 and clock out again at F13. And than it should jump to C14 than D14, E14, F14 and so on.
Can anyone help me?
I was using a macro that filled the time in line but it didn't jump to the next days.

Thanks

 

[Micron]

Try this on a copy of your sheet. You will need to change 004 to your sheet name. See the note I wrote about assuming that in any row with time data, C will not be blank (so no value in C means no value in D,E or F either. You may need to turn off events if this code causes other events to run and you don't want that to happen.

Sub FindEmptyInRange()
Dim ws As Worksheet, rng As Range
Dim Lrow As Long

Set ws = Sheets("004")
'not guaranteed but asssume last row will have a value in column A
Lrow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
Set rng = ws.Cells.Find("", After:=Range("C" & Lrow), LookAt:=xlPart, LookIn:=xlFormulas, _
           SearchOrder:=xlByRows, SearchDirection:=xlNext)
If rng.Column <= 7 And rng.Column >= 3 Then
    rng = Time
Else
    ws.Range("C" & rng.Row + 1) = Time
End If

Set rng = Nothing
Set ws = Nothing

End Sub

 

[poupinhas]

It works like a

Try this on a copy of your sheet. You will need to change 004 to your sheet name. See the note I wrote about assuming that in any row with time data, C will not be blank (so no value in C means no value in D,E or F either. You may need to turn off events if this code causes other events to run and you don't want that to happen.

Sub FindEmptyInRange()
Dim ws As Worksheet, rng As Range
Dim Lrow As Long

Set ws = Sheets("004")
'not guaranteed but asssume last row will have a value in column A
Lrow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
Set rng = ws.Cells.Find("", After:=Range("C" & Lrow), LookAt:=xlPart, LookIn:=xlFormulas, _
           SearchOrder:=xlByRows, SearchDirection:=xlNext)
If rng.Column <= 7 And rng.Column >= 3 Then
    rng = Time
Else
    ws.Range("C" & rng.Row + 1) = Time
End If

Set rng = Nothing
Set ws = Nothing

End Sub

It works like a charm. Thank you very much :D

 

[Micron]

You're welcome. Maybe mark this one as solved to save others time when looking for threads to solve?

 

[Aiden Jenner]

You're welcome. Maybe mark this one as solved to save others time when looking for threads to solve?

Hello all,

Thank you for the above suggestion it's great.
Please can you advise on how to add another 2 columns for a third break and do it so that it goes to today's date rather than the next available date.

thank you

 

[Micron]

I suggest that you start your own thread to get maximum exposure, rather than adding your issue to this one (that's called hijacking a thread). If you think anything in this thread would be helpful to anyone, you can post a link to this one.

 

[Aiden Jenner]

You're welcome. Maybe mark this one as solved to save others time when looking for threads to solve?

Apologies, I will do that.
Your answer to the original query was so succinct and useful I didn't want to seem repetitive.

But thank you definitely don't want to be hijacking anything. Sorry.