You don't show a declaration for LastRow, so am assuming it is declared at the module level, if not then that code should error.
But it is not a good idea to depend on widely scoped variables to define the target of an update. Much better to expressly go find the information just before the update using Find
Private Sub CommandButton1_Click() Dim r As Excel.Range Dim UpdateRow As Long ' Find which row to update based on Employee ID UpdateRow = Sheets("Data").Range("A" & Sheets("Data").Count).End(xlUp).Row Set r = Sheets("Data").Range("A1:A" & UpdateRow).Find(What:=Format(txtEmployeeID.Text), Lookat:=xlWhole, MatchCase:=False) If Not r Is Nothing Then ' If Not Nothing means ID was found - store the row reference UpdateRow = r.Row Else ' Not suggesting you do this but just adding a new item ' if text not found, as an example... UpdateRow = UpdateRow + 1 End If ' Saving time - only 2 textboxes as an example With Sheets("Data") .Cells(UpdateRow, 1).Value = Format(txtEmployeeID.Text) .Cells(UpdateRow, 4).Value = StrConv(txtSurname.Text, vbProperCase) End With End Sub
You can adapt the same logic to the Search. Much more efficient than looping.