Search using a form controls excel vba

0

Dear Sir, Thank you for your response on the first question.  I am a novice in vba and programming whatsoever. I saw videos of excel vba and I was thrilled I then decided to try and reproduce what I saw.

Now the issues:  (A)  I have a workbook with two sheets "Staff_Records" and "Search_Results"  The first sheets(Staff_Records) got data input from UserForm1, add data codes :

Private Sub addData_btn_Click()
Dim NewRow As Long

Worksheets("Staff_Records").Activate

NewRow = Worksheets("Staff_Records").Range("B65356").End(xlUp).Row + 1

Cells(NewRow, 1) = txtFname.Value
Cells(NewRow, 2) = txtLname.Value
Cells(NewRow, 3) = txtGender.Value
Cells(NewRow, 4) = txtAddress.Value
Cells(NewRow, 5) = txtDoE.Value
Cells(NewRow, 6) = txtQLF.Value
Cells(NewRow, 7) = txtDPT.Value
Cells(NewRow, 8) = txtMarital.Value
Cells(NewRow, 9) = txtID.Value
Cells(NewRow, 10) = txtGrade.Value
Cells(NewRow, 11) = txtState.Value
Cells(NewRow, 12) = txtPhone_Num.Value
Cells(NewRow, 13) = txtNexKin_Name.Value
Cells(NewRow, 14) = txtNexkin_Num.Value



listDisplay.ColumnCount = 14
listDisplay.RowSource = "A1:N65356

This works very well.

(B) I THEN TRIED to create a separate form to search for data using ID_NUM as a search criteria from this sheet which should  be display on listbox and equally stored on sheet2(Search_Results). When I tried running code I got Data not found. see codes:

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 Cells(ColNum, 1).Value = ""
If InStr(1, Cells.Value, txtSearch_Word.Value) = ID_Num 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

ColNum = ColNum + 1

End If


Loop


listDisplay.RowSource = "Search_Results!A5:N43"
If ColNum = 1 Then
MsgBox ("Data Not Available")
End If

End Sub

PLEASE HELP ME OUT. Also steps to take to understand programming if any beginners to intermediate tutorial links  available free for now.

Thank you so much.

Answer
Discuss

Discussion

It would be easier if you could provide a sample workbook however you have your do - loop breaker inside your If - end if. Once the IF returns false it will repeat itselfe for ever.    
k1w1sm (rep: 167) Sep 9, '19 at 4:53 pm
 I appreciate all of you who have tried to provide solutions to my query. But it hasn't work yet.
I said on my sheet1 the rows with data start on row 5 while the target or search criterial is on column 9, Cell range is A5:N43. Below is the worksheet again:

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                      
Akees Sep 12, '19 at 7:07 am
Add to Discussion

Answers

0

I have had a look at your followup post and also had a few thoughts about my previous answer which I am about to replace. You seem to be confused about what is a row and what is a column you state that your data on the staff sheet starts in row5(A5) "A5" is row 1 column 5

Columns are the up and down bits often seen in real life holding up  bridges if that helps.

Looking at the data sample you provided it looks like you have a heading row. This would be row 1. Your data would then start in "E2" 

So the following code will be based on those assumtions

Private Sub cmdbtn_Enter_Click()
Dim ColNum As Long
Dim ID_Num As String
Dim NewRow As Long
Dim ColCount As Integer
Dim StaffRow As Integer
Dim StaffCol As Integer
Dim DataFound As Boolean

' set the staff start row and column - change this if I have not got this right
StaffRow = 2
StaffCol = 5


NewRow = Worksheets("Search_Results").Cells(Rows.Count).End(xlUp).Row 'put next found record on next empty row

'set boolean
DataFound = False
Do Until Worksheets("Staff_records").Cells(StaffRow, 0 + StaffCol).Value = ""   ' This assumes that you will always have data in the first data column for each entry
       
     If Worksheets("Staff_records").Cells(StaffRow, 8 + StaffCol).Value = TxTSearch_word.Value Then  '
        DataFound = True
        For ColCount = 1 To 14
            Worksheets("Search_Results").Cells(NewRow, ColCount) = Worksheets("Staff_records").Cells(StaffRow, ColCount + 4)
        Next
        NewRow = NewRow + 1

    End If

    StaffRow = StaffRow + 1
Loop

' I have no idea what this next line does. use of forms requires more advanced skills than I posses
listDisplay.RowSource = "Search_Results!A1:N43"
If Not DataFound Then
MsgBox ("Data Not Available")
End If

End Sub
Discuss

Discussion

Hi Akees
Can you just confirm this is not working and what bit is not working?
As I have commented the only bit that I don't know how to get working is the writing back to the form.
The search should find the matching ID
The Macro should update the search_results sheet
k1w1sm (rep: 167) Sep 13, '19 at 6:12 pm
Add to Discussion

Answer the Question

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