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.