VBA Programming. How to transfer data from userform to specific rows and columns to a sheet?

Question

I am currently working on an alternative attendance monitoring system as an initiative. Currently, I have designed my userform which looks like the one below: Time Stamp Userform

It works like this:

1. The employee will choose the type of time stamp he/she will be using: time in, time out, 1st break start, 1st break end, 2nd break start, or 2nd break end.

2. Then the personnel number will be inputted in the personnel barcode field.

Currently, I have this code:

Private Sub CancelButton_Click()
    Unload Me
End Sub

Private Sub ClearButton_Click()
    Call UserForm_Initialize
End Sub

Private Sub OKButton_Click()
    Dim emptyRow As Long

    'Make Sheet1 active
    Sheet1.Activate

    'Determine emptyRow
    emptyRow = WorksheetFunction.CountA(Range("B:B")) + 1

    'Transfer information

    If TimeOptionButton1.Value = True Then
        Cells(emptyRow, 5).Value = "Yes"
    End If
    If TimeOptionButton2.Value = True Then
        Cells(emptyRow, 7).Value = "Yes"
    End If
    If BreakOptionButton1.Value = True Then
        Cells(emptyRow, 9).Value = "Yes"
    End If
    If BreakOptionButton2.Value = True Then
        Cells(emptyRow, 11).Value = "Yes"
    End If
    If BreakOptionButton3.Value = True Then
        Cells(emptyRow, 14).Value = "Yes"
    End If
    If BreakOptionButton4.Value = True Then
        Cells(emptyRow, 16).Value = "Yes"
    End If

    Cells(emptyRow, 2).Value = BarcodeTextBox.Value
End Sub

Private Sub UserForm_Initialize()
'Set Time In as default
    TimeOptionButton1.Value = True

    'Empty BarcodeTextBox
    BarcodeTextBox.Value = ""
End Sub

I am not really an expert in VBA coding, I just searched the codes online. The code above determines the next empty row where the data will be inputted. After filling out the userform, results in the sheet looks like this: Results

As you can see, timestamps for 1 employee is inputted on multiple rows.

However, what I wanted is only 1 row will be dedicated to one employee number so that all of his/her timestamps are all in 1 row and not in multiple rows. I hope someone can help me on this. I thought of having a code wherein the inputted personnel barcode will be searched if it's already on the sheet, and if it is, the time stamps will be inputted on the same row. I wanted it to be on 1 row so that formulas can be easily formulated such as computing for the overbreaks and the number of headcounts.

Thank you very much in advance for the good samaritan! :)

Answer 1

With minimal change to your code, try changing the line

`emptyRow = WorksheetFunction.CountA(Range("B:B")) + 1`

Into this:

Dim rFound as Range: Set rFound = Range("B:B").Find(BarcodeTextBox.Value, , , xlWhole)
If rFound Is Nothing then
    emptyRow = Range("B" & Rows.Count).End(xlUp).Row + 1
Else
    emptyRow = rFound.Row
End If