Find Data in a table and enter =now into a certain cell

[Panda]

Hey All Me again,

I need some help with some VBA code…I want to find the employee name and if their ‘In/Out’ Status is ‘Out’ and the ‘Time Stamp In’ = blank, Enter ‘=now()’ into the empty cell within ‘Time Stamp In’. Below is an example of the data within my spreadsheet.

Can anyone help me? I am sure it is easy but cant get my head around it.

Thanks in advance

Phil


Column A Column B Column C Column D

Employee Name In/Out Time Stamp Out Time Stamp In

Dave Out 17/04/2011 13:21

Mark In 16/04/2011 14:00

[shrivallabha]

This can be done using by the following code and since this is worksheet event code, you need to right click on the worksheet where you need to implement and choose "View Code" option. Then paste the code in the VBA module that will open:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
If UCase(Target.Value) = "IN" Then
With Target.Offset(, 2)
.Formula = "=Now()"
.Value = Target.Offset(, 2).Value
.NumberFormat = "d/m/yyyy h:mm"
End With
ElseIf UCase(Target.Value) = "OUT" Then
With Target.Offset(, 1)
.Formula = "=Now()"
.Value = Target.Offset(, 1).Value
.NumberFormat = "d/m/yyyy h:mm"
End With
End If
End If
End Sub

[Panda]

Thanks for your reply,

Is there a way this code can be modified to;

- search for the employee name

- check the In/Out status and if it is out and the Time In Stamp is empty then input the =now formula.

The reason why I ask this is beacue the spreadsheet I am working on uses records every time an employee signs in and out so there will be duplicate occurances of the same name.

Thanks again

Phil

[Panda]

Bump...Can anyone help me with this?

[shrivallabha]

I think, I misread your requirement, my apologies. This macro whenever you will run that is will:
1. Look for "out" entry in column "B"
2. Check if Time Stamp In is empty and will update:
Option Explicit
Public Sub SearchandUpdate()
Dim lLastRow As Long
Dim i As Integer
lLastRow = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lLastRow 'Considering your data starts at row 2
If UCase(Range("B" & i).Value) = "OUT" And Range("D" & i).Value = "" Then
With Range("D" & i)
.Formula = "=Now()"
.Value = .Value 'Comment this out if you want to retain formula instead of value
.NumberFormat = "d/m/yyyy h:mm"
End With
End If
Next i
End Sub

If still way off the mark then let us know.

[Panda]

Thanks it works a treat, but say I have the one Employee's name in Worksheet 2 Cell B9, how would I modify the code detailed above to search the data table for the name in Worksheet 2 Cell B9?

Thanks again for your help

[shrivallabha]

Are you interested in only one cell B9 or are we looking @ tip of the iceberg? If you have any requirements other than this (especially associated with this) then it will not be a bad idea to create a sample spreadsheet (the way you would like it to be). It will help you get a better solution!