Excel VBA Userform with search box

Good afternoon. I am new to this forum and extremely new to VBA. I've searched this forum, and also did a Google search for answers, and am coming up short. I hope I'm posting question this correctly!

Using Excel 2013, I currently have a spreadsheet, with a total of 40 columns, where injury data is recorded. I stumbled through creating a Userform to make it easier to enter all that is required.

Entries in the spreadsheet are summarized in the list box. The list box does not display all 40 columns; rather, only the most crucial information from 13 select columns (not all are adjacent to one another) from the spreadsheet.

If an entry needs to be modified, the user double clicks on the record displayed in the list box, it is loaded back into the User Form, and the appropriate edit can be made and saved back to the original spreadsheet.

I was recently asked to add a search box, allowing users to quickly search through/find entries on the spreadsheet. The search criteria is limited to these three columns: Last Name (column C), First Name (Column D), and Staff ID (Column E). I've added a Command Button next to the search box (txtSearch) that, once clicked, would search for and filter the items displayed in the list box based on the entry in the search box.

I used the following code, associated with the Command Button, but receive a "subscript out of range" error (run time '9'):

Private Sub CommandButton5_Click()
Dim iSearch As Long, i As Long


iSearch = Worksheets("Sheet2").Range("A1").CurrentRegion.Rows.count
For i = 40 To iSearch


If Trim(Sheet2.Cells(i, 1)) <> Trim(txtSearch.Text) And i = iSearch Then
MsgBox ("No record found")
txtSearch.Text = ""
txtSearch.SetFocus


End If


If Trim(Sheet2.Cells(i, 1)) = Trim(txtSearch.Text) Then
TextBox4.Text = Sheet2.Cells(i, 1)
txtDate.Text = Sheet2.Cells(i, 2)
txtLast.Text = Sheet2.Cells(i, 3)
txtFirst.Text = Sheet2.Cells(i, 4)
txtID.Text = Sheet2.Cells(i, 5)
txtDOB.Text = Sheet2.Cells(i, 6)
txtTitle.Text = Sheet2.Cells(i, 7)
cboCo.Text = Sheet2.Cells(i, 
cboInjury.Text = Sheet2.Cells(i, 9)
cboStaff.Text = Sheet2.Cells(i, 10)
cboFiled.Text = Sheet2.Cells(i, 11)
Exit For
End If
Next i


End Sub

The error seems to focus on this line of code: iSearch = Worksheets("Sheet2").Range("A1").CurrentRegion.Rows.count

I am really lost, and would appreciate some guidance on what I'm doing wrong.

Thank you.

Thanks for the suggestion. And I apologize that I didn't post properly.

The suggested modification still generates an error; however, after much digging and searching, I think I figured out my mistake. I need an additional step, an advanced filter, in order to finish my project.

I'm still learning, and testing my latest assumption. If/when I get it, I will post an update.

Hope I'm on the right track.

Greetings. During a brief hospital stay, I had lots of time to dig into this project and the issue previously experienced. Thanks to much of the information in this Forum, and a couple of of others, I've solved the initial issue by utilizing a helper sheet. Thanks for that information.

I am encountering a new, different issue with this almost completed project, but will post as a new issue and attach the file as it exists now (still much more work to be done before it's complete).

Thanks everyone!