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 = ""
- 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.