Look for an ID in column "C" and match date in row "I", then insert time in same row

[graudales]

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":

[TABLE="width: 401"]
<tbody>[TR]
[TD]Badge No.[/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]Time In[/TD]
[TD]Code[/TD]
[/TR]
[TR]
[TD="align: right"]101[/TD]
[TD][/TD]
[TD="align: right"]2/19/2018[/TD]
[TD="align: right"]6:05:00 AM[/TD]
[TD]IN[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD][/TD]
[TD="align: right"]2/19/2018[/TD]
[TD="align: right"]5:55:00 AM[/TD]
[TD]IN[/TD]
[/TR]
[TR]
[TD="align: right"]101[/TD]
[TD][/TD]
[TD="align: right"]2/19/2018[/TD]
[TD="align: right"]11:02:00 AM[/TD]
[TD]LO[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD][/TD]
[TD="align: right"]2/19/2018[/TD]
[TD="align: right"]11:09:00 AM[/TD]
[TD]LO[/TD]
[/TR]
[TR]
[TD="align: right"]101[/TD]
[TD][/TD]
[TD="align: right"]2/19/2018[/TD]
[TD="align: right"]11:42:00 AM[/TD]
[TD]LI[/TD]
[/TR]
[TR]
[TD="align: right"]104[/TD]
[TD][/TD]
[TD="align: right"]2/19/2018[/TD]
[TD="align: right"]11:59:00 AM[/TD]
[TD]LI[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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

[TABLE="width: 1121"]
<colgroup><col><col><col span="6"><col><col span="7"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"]2/19/2018[/TD]
[TD="colspan: 7"]2/20/2018[/TD]
[/TR]
[TR]
[TD]Badge No.[/TD]
[TD]Name[/TD]
[TD]IN[/TD]
[TD]LO[/TD]
[TD]LI[/TD]
[TD]CO[/TD]
[TD]TOT L[/TD]
[TD]TOT HR[/TD]
[TD]TOTAL WORKED HRS[/TD]
[TD]IN[/TD]
[TD]LO[/TD]
[TD]LI[/TD]
[TD]CO[/TD]
[TD]TOT L[/TD]
[TD]TOT HR[/TD]
[TD]TOTAL WORKED HRS[/TD]
[/TR]
[TR]
[TD="align: right"]100[/TD]
[TD]ANGELA[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD="align: right"]101[/TD]
[TD]JOSE[/TD]
[TD="align: right"]6:05:00[/TD]
[TD="align: right"]11:02:00[/TD]
[TD="align: right"]11:42:00[/TD]
[TD="align: right"]14:36:00[/TD]
[TD="align: right"]0:40:00[/TD]
[TD="align: right"]8:31:00[/TD]
[TD="align: right"]7:51:00[/TD]
[TD="align: right"]6:32:00[/TD]
[TD="align: right"]10:57:00[/TD]
[TD="align: right"]11:37:00[/TD]
[TD="align: right"]14:32:00[/TD]
[TD="align: right"]8:00:00[/TD]
[TD="align: right"]0:40:00[/TD]
[TD="align: right"]7:20:00[/TD]
[/TR]
</tbody>[/TABLE]


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.

 

[Fluff]

How about

Sub TimeClock()
   Dim Rw As Range
   Dim Col1 As Range
   Dim Col2 As Range
   
   Set Rw = Range("A:A").Find([COLOR=#ff0000]BOUT[/COLOR].Text, , , xlWhole, , , , , False)
   Set Col1 = Range("1:1").Find(Date, , , xlWhole, , , , , False)
   Set Col2 = Col1.MergeArea.Resize(2).Find([COLOR=#ff0000]CODE[/COLOR].Text, , , xlWhole, , , , , False)
   Cells(Rw.Row, Col2.Column).Value = Time
   
End Sub

Change the values in red to match your textboxes

 

[graudales]

Hello Fluff, I really appreciate your fast response, but is not working and I am sure I most be doing something wrong...
I have 4 command buttons and each command button calls a different user form, each "ok" button on each userform has a code on it and depends which command button the employee selects in the first screen that will determine the 2 letter code that needs to be added automatically.
Now I just realize that I need to match a third cell in order to select where the time needs to be input...because is not only the date and Id now also needs to match the column that has the same description.....Lets say the employee select the command button that will clock out for lunch, so the macro will need to find the row that has the id + today's date + the column that match the description "Clock out for lunch" or LO...so now it is more complicated, I wanted to past a screen shoot but I cant

 

[Fluff]

Why not just have one userform with a combobox containing the clock in/out codes which the user can then select?

 

[graudales]

Thank you, i thought about that option too.
So this is what I have:
The employee will scan his badge in the first text box, then a combo box will be right below so he can select the code from the 4 options...they will clock in in at 6 AM, in row 2 column C the textbox will input the id badge, then i can assign column D for "clock in" time and I can autopopulate that time with the vba code, then I can assign column E for the "clock out for lunch time", assign column F for the "clock in from lunch time" and assign column G for the "clock out from shift time"....then 4 people will clock in..I can do this firts step, I know how to ask the program to input the text box in a cell and to add the time in the following cell, but how do i ask the program to look for that id and for today's date and find the column for the especific option that they are selecting and input the time in that specific column? I think this where I hit the wall first and then I decided to go with the simple user form that just finds the first empty row and input there the info, I dont know how to find the Id and match it to find the row and the column where the other times should be input. Thank you again.

 

[Fluff]

Using this layout, the code I supplied should do that.

Excel 2013 32 bit
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			2/19/2018							2/20/2018
2	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
3	100	ANGELA	-	-	-	-	-	-	-	-	-	-	-	-	-	-
4	101	JOSE	06:05:00	19:22:14	11:42:00	14:36:00	00:40:00	08:31:00	07:51:00	06:32:00	10:57:00	11:37:00	14:32:00	08:00:00	00:40:00	07:20:00
Sheet2

The dates in row 1 will need to be in merged cells, ie 19th feb will need to be merged C1:I1

 

[graudales]

Thank you Fluff,
You have to know that i am very new on VBA..I am trying to set up a range because col1` and col2 dont work for some reason, look to the code I have I am sure I have a issue:


Private Sub OKOPTION_Click()
Dim Rw As Range
Set ws = Sheets("Data")


Dim Col1 As Range
Dim Col2 As Range

Set Rw = Range("A:A").Find(BADGEID.Text, , , xlWhole, , , , , False)
Set Col1 = Range("1:1").Find(Date, , , xlWhole, , , , , False)
Set Col2 = Col1.MergeArea.Resize(2).Find(OPTIONS.Text, , , xlWhole, , , , , False)
Cells(Rw.Row, Col2.Column).Value = Time
End Sub


Private Sub UserForm_Click()


End Sub


Private Sub UserForm_Initialize()
With OPTIONS
.AddItem "CI"
.AddItem "LO"
.AddItem "LI"
.AddItem "CO"
End With




End Sub
I have the tab "scan badge" where i have the user form and i dont know how to put together the sheet 2 "data" inside the code that you sent me...

 

[graudales]

Hello Fluff, I am reading the code you sent me and it makes total sense...
I think on the first line of your code will look for the value in the textbox named "BOUT" in column A, that will return the row where the ID is, then the will look for today´s date on the second line of the code, finally on the third line will look for the value from the ComboBox that you recommended before named "CODE" that will contain the 2 letter code that the employee is selecting and then will input the time on that intersection of ROW,COL.
Now here is where I am stuck because I want the user form to be displayed in a different tab than the tab that actually has the information.. I don´t want everybody to have access to look other employees times,...I add the changes to your code, but is giving me error ;( ;( ...how do I set these ranges correctly?
Thank you so much for your help.

Sub TimeClock()
Dim Rw As Range
Dim Col1 As Range
Dim Col2 As Range

Set Rw = worksheets("data").Range("A:A").Find(BOUT.Text, , , xlWhole, , , , , False)
Set Col1 = worksheets("data").Range("1:1").Find(Date, , , xlWhole, , , , , False)
Set Col2 = Col1.MergeArea.Resize(2).Find(CODE.Text, , , xlWhole, , , , , False)
Cells(Rw.Row, Col2.Column).Value = Time

End Sub

 

[Fluff]

You also need to add the sheet name to the last line

worksheets("data").Cells(Rw.Row, Col2.Column).Value = Time

 

[graudales]

Thank you again Fluff....
One more thing, the code is giving me error on the third line :
Set Col2 = Col1.MergeArea.Resize(2).Find(CODE.Text, , , xlWhole, , , , , False)

i have no idea why ... Do you think is missing an specific range like the other lines? thank you