Hi
I have written a code that searches for a database for employee ID. However, the message box appears multiple time
I want my code to do the following:
1. If employee ID exists, copy and paste the necessary info
2. If employee ID is not found, a message box appears once 'No records found'
I have written a code that searches for a database for employee ID. However, the message box appears multiple time
I want my code to do the following:
1. If employee ID exists, copy and paste the necessary info
2. If employee ID is not found, a message box appears once 'No records found'
VBA Code:
Private Sub CommandButton3_Click()
Dim inputEmployeeID As String, EmployeeID As String
EmployeeID = Trim(TextBox1.Text)
Dim srcWS As Worksheet, desWS As Worksheet, lastrow As Long, i As Integer, erow As Long
Set srcWS = Workbooks("Master Database.xlsm").Worksheets(" Database")
Set desWS = Workbooks("Employee_WB.xlsm").Worksheets("Employee_WS")
'Find filled row in database
lastrow = srcWS.Cells(Rows.Count, 1).End(xlUp).Row
'Find first empty row in database
erow = desWS.Cells(Rows.Count, 2).End(xlUp).Row + 1
inputEmployeeID = TextBox1.Text
For i = 2 To lastrow
If EmployeeID = srcWS.Cells(i, 1).Value Then
srcWS.Cells(i, 1).Copy
desWS.Cells(erow, 2).PasteSpecial Paste:=xlPasteValues
srcWS.Cells(i, 2).Copy
desWS.Cells(erow, 3).PasteSpecial Paste:=xlPasteValues
srcWS.Cells(i, 3).Copy
desWS.Cells(erow, 4).PasteSpecial Paste:=xlPasteValues
srcWS.Cells(i, 4).Copy
desWS.Cells(erow, 5).PasteSpecial Paste:=xlPasteValues
Exit For
erow = erow + 1
Else
MsgBox ("No record found")
End If
Next i
TextBox1.Value = ""
UserForm1.Hide
End Sub