Hello, I am new on VBA and all I know is been from reading this website and again I hit the wall... 
I hope that you can help me on this one:
We have a database that collects the time that the employee is actually working, so far I have a userform that has 4 options where the employee can select what is he doing, it can be clocking in, going out for lunch, coming back from lunch or going home, then after they scan their id badge and click ok the macro auto populates the date and time and a 2 letter code which can be IN,LO,LI or CO depending the option they selected, it looks like this this is the tab "data":
<tbody>
</tbody>
this is the code I have :
Private Sub OKOUT_Click()
Dim LastRow As Long, ws As Worksheet
Set ws = Sheets("Data")
LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row
ws.Range("B" & LastRow).Value = BOUT.Text 'Adds the TextBox3 into Col A & Last Blank Row
ws.Range("d" & LastRow).Value = Date
ws.Range("e" & LastRow).Value = Time
ws.Range("f" & LastRow).Value = "CO"
Unload Me
End Sub
I have a second tab that collects all this info with index match, the tab name is "whole report" and basically look for the id, the date and copy the time in the matrix, this tab has 14 days on the top which is how the employee is getting paid and each date is brake down in 4 columns click in, lunch out, lunch in and clock out, then at the end I have a columns that subtrac the lunch time from the whole time to get the total amount of hours that the employee works per day and looks like this:
formula: =IFERROR((INDEX(DATA!$E$3:$E$899988,MATCH($E$1&$C8&"CO",DATA!$D$3:$D$899988&DATA!$B$3:$B$899988&DATA!$F$3:$F$899988,0))),"-")
C D E F G H I J K L M N O P Q
<colgroup><col><col><col span="6"><col><col span="7"></colgroup><tbody>
</tbody>
now the spread sheet is extremely slow and I was hoping that instead of have the tab with formulas there may be a code that match the id from the text box and match the date and then autopopulate the system's time in the cell in the same row.
I was thinking in something like:
vlookup up on range("whole report" C3:C10000)=textbox.value AND find today's date in row "1" on the tab "whole report", then input time in the intersection where that row and that column match my formula.
I have the idea in my head but I don't know how to write it in VBA code and I don't even know if this will be possible, thank you so much for your help.
I hope that you can help me on this one:We have a database that collects the time that the employee is actually working, so far I have a userform that has 4 options where the employee can select what is he doing, it can be clocking in, going out for lunch, coming back from lunch or going home, then after they scan their id badge and click ok the macro auto populates the date and time and a 2 letter code which can be IN,LO,LI or CO depending the option they selected, it looks like this this is the tab "data":
| Badge No. | Date | Time In | Code | |
| 101 | 2/19/2018 | 6:05:00 AM | IN | |
| 104 | 2/19/2018 | 5:55:00 AM | IN | |
| 101 | 2/19/2018 | 11:02:00 AM | LO | |
| 104 | 2/19/2018 | 11:09:00 AM | LO | |
| 101 | 2/19/2018 | 11:42:00 AM | LI | |
| 104 | 2/19/2018 | 11:59:00 AM | LI | |
<tbody>
</tbody>
this is the code I have :
Private Sub OKOUT_Click()
Dim LastRow As Long, ws As Worksheet
Set ws = Sheets("Data")
LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row + 1 'Finds the last blank row
ws.Range("B" & LastRow).Value = BOUT.Text 'Adds the TextBox3 into Col A & Last Blank Row
ws.Range("d" & LastRow).Value = Date
ws.Range("e" & LastRow).Value = Time
ws.Range("f" & LastRow).Value = "CO"
Unload Me
End Sub
I have a second tab that collects all this info with index match, the tab name is "whole report" and basically look for the id, the date and copy the time in the matrix, this tab has 14 days on the top which is how the employee is getting paid and each date is brake down in 4 columns click in, lunch out, lunch in and clock out, then at the end I have a columns that subtrac the lunch time from the whole time to get the total amount of hours that the employee works per day and looks like this:
formula: =IFERROR((INDEX(DATA!$E$3:$E$899988,MATCH($E$1&$C8&"CO",DATA!$D$3:$D$899988&DATA!$B$3:$B$899988&DATA!$F$3:$F$899988,0))),"-")
C D E F G H I J K L M N O P Q
| 2/19/2018 | 2/20/2018 | ||||||||||||||
| Badge No. | Name | IN | LO | LI | CO | TOT L | TOT HR | TOTAL WORKED HRS | IN | LO | LI | CO | TOT L | TOT HR | TOTAL WORKED HRS |
| 100 | ANGELA | - | - | - | - | - | - | - | - | - | - | - | - | - | - |
| 101 | JOSE | 6:05:00 | 11:02:00 | 11:42:00 | 14:36:00 | 0:40:00 | 8:31:00 | 7:51:00 | 6:32:00 | 10:57:00 | 11:37:00 | 14:32:00 | 8:00:00 | 0:40:00 | 7:20:00 |
<colgroup><col><col><col span="6"><col><col span="7"></colgroup><tbody>
</tbody>
now the spread sheet is extremely slow and I was hoping that instead of have the tab with formulas there may be a code that match the id from the text box and match the date and then autopopulate the system's time in the cell in the same row.
I was thinking in something like:
vlookup up on range("whole report" C3:C10000)=textbox.value AND find today's date in row "1" on the tab "whole report", then input time in the intersection where that row and that column match my formula.
I have the idea in my head but I don't know how to write it in VBA code and I don't even know if this will be possible, thank you so much for your help.
Last edited:
