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

G

graudales

New Member
Joined
Nov 9, 2015
Messages
19
Hello, I am new on VBA and all I know is been from reading this website and again I hit the wall... :confused::confused: 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.
 
Last edited:
Sort by date Sort by votes
What is the name of your combobox?
 
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
 
Upvote 0
Does that now work?

If not do you get any errors?
 
Last edited:
Upvote 0
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? :(
 
Upvote 0
Are your dates in row 1 in merged cells, as mentioned in post#6
 
Upvote 0
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
 
Upvote 0
Add the msgbox as shown
Code: 
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?
 
Upvote 0
Ok run this
Code: 
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Deleting multiple rows, columns, unmerging, pivot table all at once.
Replies
2
Views
98
HighAndWilder
H
P
  • Question Question
Offsetting dates / usage in vlookup or index/match function
Replies
1
Views
547
GlennUK
G
airforceone
  • Solved
Search Row 1 for Text if Found Convert Column to Date
2
Replies
11
Views
1K
airforceone
airforceone
A
  • Question Question
copy amount before TOTAL row for specific column based on matching names in column with sheet name
Replies
0
Views
284
Alaa mg
A
M
  • Question Question
VBA code to add date/time and username if any cell in same row is changed for rows 2 down.
Replies
3
Views
3K
JW00
J

Forum statistics

Threads
1,223,884
Messages
6,175,173
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top