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]

What is the name of your combobox?

 

[graudales]

OPTIONS is the name.

Private Sub OKOPTION_Click()
Dim Rw As Range


Dim Col1 As Range
Dim Col2 As Range

Set Rw = Worksheets("whole report").Range("A:A").Find(BADGEID.Text, , , xlWhole, , , , , False)
Set Col1 = Worksheets("whole report").Range("1:1").Find(Date, , , xlWhole, , , , , False)
Set Col2 = Col1.MergeArea.Resize(2).Find(OPTIONS.Text, , , xlWhole, , , , , False)
Worksheets("whole report").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

 

[Fluff]

Does that now work?

If not do you get any errors?

 

[graudales]

sorry Fluff, I didnt get any mg...Is not working, the code I sent is the one that is giving me the error on third line:

Set Col2 = Col1.MergeArea.Resize(2).Find(OPTIONS.Text, , , xlWhole, , , , , False)

what do I need to chance?

 

[Fluff]

Are your dates in row 1 in merged cells, as mentioned in post#6

 

[graudales]

yes sir, the dates on top are merged from E:K first group, then L:R, then S:Y...

 

[graudales]

now i change the date from formula to actually date.. i had a formula on the first cell and the following cells just had the formula so I changed that and now the error is in the 4th line :

Worksheets("whole report").Cells(Rw.Row, Col2.Column).Value = Time

 

[Fluff]

Add the msgbox as shown

Private Sub OKOPTION_Click()
   Dim Rw As Range
   Dim Col1 As Range
   Dim Col2 As Range
   
   Set Rw = Worksheets("whole report").Range("A:A").Find(BADGEID.Text, , , xlWhole, , , , , False)
   Set Col1 = Worksheets("whole report").Range("1:1").Find(Date, , , xlWhole, , , , , False)
   Set Col2 = Col1.MergeArea.Resize(2).Find(OPTIONS.Text, , , xlWhole, , , , , False)
   MsgBox Rw.Address & vbLf & Col1.Address & vbLf & Col2.Address
   Worksheets("whole report").Cells(Rw.Row, Col2.Column).Value = Time
End Sub

What does the msgbox say?

 

[graudales]

error on that line now, debug again..

MsgBox Rw.Address & vbLf & Col1.Address & vbLf & Col2.Address

 

[Fluff]

Ok run this

Private Sub OKOPTION_Click()
   Dim Rw As Range
   Dim Col1 As Range
   Dim Col2 As Range
   
   Set Rw = Worksheets("whole report").Range("A:A").Find(BADGEID.Text, , , xlWhole, , , , , False)
   If Rw Is Nothing Then MsgBox "ID not found": Exit Sub
   Set Col1 = Worksheets("whole report").Range("1:1").Find(Date, , , xlWhole, , , , , False)
   If Col1 Is Nothing Then MsgBox "Date not found": Exit Sub
   Set Col2 = Col1.MergeArea.Resize(2).Find(OPTIONS.Text, , , xlWhole, , , , , False)
   If Col2 Is Nothing Then MsgBox "Option not found": Exit Sub
   Worksheets("whole report").Cells(Rw.Row, Col2.Column).Value = Time
End Sub

What does the msgbox say?