VBA to Search and Replace??

Ive created a in/out time time clock using "Userform"
it works very well, but it's populating my spread sheet row by row. One employee may punch in at 8am and another at 2pm.
I need a code so when the 8am employee punches "OUT", the Out punch data populates in the original row (instead of creating a NEW row)

here is the code i have so far

Private Sub CommandButton1_Click() 
Dim iRow As Long 
Dim ws As Worksheet 
Set ws = Worksheets("sheet1") 

'find first empty row in database 
iRow = ws.Cells(Rows.Count, 1) _ 
  .End(xlUp).Offset(1, 0).Row 

'check for a part number 
If Trim(Me.TextBox1.Value) = "" Then 
  Me.TextBox1.SetFocus 
  MsgBox "Please enter Required Information" 
  Exit Sub 
End If 
If Trim(Me.TextBox2.Value) = "" Then 
  Me.TextBox2.SetFocus 
  MsgBox "Please enter Required Information" 
  Exit Sub 
End If 

'copy the data to the database 
ws.Cells(iRow, 1).Value = Me.TextBox1.Value 
ws.Cells(iRow, 2).Value = Me.TextBox2.Value 

ws.Cells(iRow, 3).Value = Date 
ws.Cells(iRow, 4).Value = Time() 

'clear the data 
Me.TextBox1.Value = "" 
Me.TextBox2.Value = "" 





End Sub 

Private Sub CommandButton2_Click() 
Dim iRow As Long 
Dim ws As Worksheet 
Set ws = Worksheets("sheet1") 



'find first empty row in database 
iRow = ws.Cells(Rows.Count, 1) _ 
  .End(xlUp).Offset(0).Row 


If Trim(Me.TextBox1.Value) = "" Then 
  Me.TextBox1.SetFocus 
  MsgBox "Please enter Required Information" 
  Exit Sub 
End If 
If Trim(Me.TextBox2.Value) = "" Then 
  Me.TextBox2.SetFocus 
  MsgBox "Please enter Required Information" 
  Exit Sub 
End If 

'clear the data 
Me.TextBox1.Value = "" 
Me.TextBox2.Value = "" 


ws.Cells(iRow, 5).Value = Time() 
End Sub

Hello riosaaron,

Welcome to the Forum!

I added a macro to the UserForm that will return the row the person's name is in. If they have not clocked in yet, it returns a zero. If the return value is zero then iRow defaults to the next available row.

Along with this macro, each button now checks if the person has already clocked in or out and displays a message if true. Also a new entry under the same is locked out for 24 hours. here is the macro and the updated UserForm code. All these changes have been made to the attached workbook.

Macro to Return Row Number of Person's Name

Function FindEmployeeRow(ByVal LastName As String, ByVal FirstName As String)

  Dim Matched As Range
  Dim NameRange As Range
  Dim Rng As Range
  Dim RngEnd As Range
  Dim Wks As Worksheet
  
    Set Wks = Worksheets("Sheet1")
    Set Rng = Wks.Range("A2")
    
    Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
    If RngEnd.Row < Rng.Row Then Exit Function Else Set Rng = Wks.Range(Rng, RngEnd)
    
      Set Matched = Rng.Find(LastName, , xlValues, xlWhole, xlByRows, xlNext, False)
        If Not Matched Is Nothing Then
           FindEmployeeRow = Matched.Row
        Else
           FindEmployeeRow = 0
        End If
  
End Function

Updated UserForm Code

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim R As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

'check for a part number
If Trim(Me.TextBox1.Value) = "" Then
  Me.TextBox1.SetFocus
  MsgBox "Please enter Required Information"
  Exit Sub
End If
If Trim(Me.TextBox2.Value) = "" Then
  Me.TextBox2.SetFocus
  MsgBox "OOOp's...Did you forget First or Last Name?"
  Exit Sub
End If

R = FindEmployeeRow(TextBox1, TextBox2)
If R > 0 Then iRow = R

If ws.Cells(iRow, "D") <> "" Then
  If Now() - (ws.Cells(iRow, "C") + ws.Cells(iRow, "D")) < 24 Then
     MsgBox "You have already clocked in.", vbOKOnly + vbExclamation
     Exit Sub
  End If
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.TextBox1.Value
ws.Cells(iRow, 2).Value = Me.TextBox2.Value

ws.Cells(iRow, 3).Value = Date
ws.Cells(iRow, 4).Value = Time()

'clear the data
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""

End Sub

Private Sub CommandButton2_Click()
Dim iRow As Long
Dim R As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")



'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(0).Row


If Trim(Me.TextBox1.Value) = "" Then
  Me.TextBox1.SetFocus
  MsgBox "OOOp's...Did you forget First or Last Name?"
  Exit Sub
End If
If Trim(Me.TextBox2.Value) = "" Then
  Me.TextBox2.SetFocus
  MsgBox "Please enter Required Information"
  Exit Sub
End If

R = FindEmployeeRow(TextBox1, TextBox2)
If R > 0 Then iRow = R

If ws.Cells(iRow, "E") <> "" Then
  If Now() - (ws.Cells(iRow, "C") + ws.Cells(iRow, "E")) < 24 Then
     MsgBox "You have already clocked out.", vbOKOnly + vbExclamation
     Exit Sub
  End If
End If

'clear the data
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""

ws.Cells(iRow, 5).Value = Time()
End Sub