Thank you sir for your reponse.
Private Sub cmdbtn_Enter_Click()
Dim ColNum As Long
Dim ID_Num As String
Dim NewRow As Long
NewRow = 2
ColNum = Worksheets("Search_Results").Cells(Rows.Count).End(xlUp).Row
Do Until Worksheets("Staff_records").Cells(ColNum, 1).Value = ""
'If InStr(1, Cells.Value, TxTSearch_word.Value) = ID_Num Then
If Worksheets("Staff_records").Cells(ColNum, 9).Value = txtSearch_Word.Value Then
Worksheets("Search_Results").Cells(ColNum, 1).Value = Cells(NewRow, 1).Value
Worksheets("Search_Results").Cells(ColNum, 2).Value = Cells(NewRow, 2).Value
Worksheets("Search_Results").Cells(ColNum, 3).Value = Cells(NewRow, 3).Value
Worksheets("Search_Results").Cells(ColNum, 4).Value = Cells(NewRow, 4).Value
Worksheets("Search_Results").Cells(ColNum, 5).Value = Cells(NewRow, 5).Value
Worksheets("Search_Results").Cells(ColNum, 6).Value = Cells(NewRow, 6).Value
Worksheets("Search_Results").Cells(ColNum, 7).Value = Cells(NewRow, 7).Value
Worksheets("Search_Results").Cells(ColNum, 8).Value = Cells(NewRow, 8).Value
Worksheets("Search_Results").Cells(ColNum, 9).Value = Cells(NewRow, 9).Value
Worksheets("Search_Results").Cells(ColNum, 10).Value = Cells(NewRow, 10).Value
Worksheets("Search_Results").Cells(ColNum, 11).Value = Cells(NewRow, 11).Value
Worksheets("Search_Results").Cells(ColNum, 12).Value = Cells(NewRow, 12).Value
Worksheets("Search_Results").Cells(ColNum, 13).Value = Cells(NewRow, 13).Value
Worksheets("Search_Results").Cells(ColNum, 14).Value = Cells(NewRow, 14).Value
NewRow = NewRow + 1
End If
ColNum = ColNum + 1
Loop
listDisplay.RowSource = "Search_Results!A2:N43"
If ColNum = 1 Then
MsgBox ("Data Not Available")
End If
End Sub
Code to get search result by ID_Num criteria using a search control form.
On my worksheet ID_Num is on column 9 while rows start on row5("A5").
Below is my workbook sheet1(Staff_Records)
FIRST NAME LAST NAME GENDER ADDRESS EMPLOYMENT DATE QUALIFICATION DEPARTMENT MARITAL STATUS ID_Num GRADE LEVEL STATE OF ORIGIN PHONE NUM AKEEKUE GOODLUCK MALE ELELENWO PORT HARCOURT 1/4/2014 HND CONTROL MARRIED PPSL/CS/43 SPECIALIST RIVERS 8113933170 KENNEDY ASAAN MALE OZOBA PORT HARCOURT 1/5/2014 SSCE LOGISTICS MARRIED PPSL/CS/21 B EXECUTIVE OFFICE RIVERS 8113933170 KENNEDY ASAAN MALE OZOBA PORT HARCOURT 1/5/2014 SSCE LOGISTICS MARRIED PPSL/CS/21 B EXECUTIVE OFFICE RIVERS 8113933170 KENNEDY ASAAN MALEOZOBA PORT HARCOURT
1/5/2014 SSCE LOGISTICS MARRIED PPSL/CS/21 B EXECUTIVE OFFICE RIVERS8113933170