Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Search control Form

0

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 MALE

OZOBA PORT HARCOURT

1/5/2014 SSCE LOGISTICS MARRIED PPSL/CS/21 B EXECUTIVE OFFICE RIVERS

8113933170

Answer
Discuss

Discussion

If this is your response to k1w1sm's answer in your original question, then you should simply edit your Question as needed there and put a response to his Answer in the Discussion section under the question.
don (rep: 1989) Sep 10, '19 at 6:11 am
Thanks Don I will have another guess at what Akees is trying to achive and address that in the original post should you wish to kill this one.
k1w1sm (rep: 197) Sep 10, '19 at 5:02 pm
Add to Discussion



Answer the Question

You must create an account to use the forum. Create an Account or Login