Creating a search function

[Aaron Bean]

Hi, I am trying to create a search function for my spreadsheet. I have created the code that takes the users input i.e. 200 and searches the worksheet for it and returns all of the different rows that have the value of 200 for example. See code below. I now need to be able to let the user select an item the search function returns to the list box and then either click on a button to edit it which would then bring up the data input userform and populate it from the details of the selected item from the worksheet, or simply just go to that row on the worksheet.

Any ideas how I can link the row number to a hidden list box column and then use this to enter the details back into the userform ready to be edited by the user?

Thanks for your help. Aaron


Private Sub Search_Click()

Dim sht As Worksheet
Dim lngMaxRows As Long, lngRow As Long 'lngMaxRows = total rows
Dim lngItem As Long
Dim SearchTextBox As Long

Sheets("Month").Activate
Set sht = ActiveSheet
QuerySearch.DisplayResults.Clear ' Clears the display results listbox in userform
lngMaxRows = Cells(65535, 13).End(xlUp).Row ' Works out what the last row is

For lngRow = 1 To lngMaxRows ' Checks from row 1 to lngMaxRow defined above
If QuerySearch.SearchTextBox.Text = sht.Cells(lngRow, 13).Value Then
DisplayResults.AddItem ""
lngItem = QuerySearch.DisplayResults.ListCount - 1
QuerySearch.DisplayResults.ColumnHeads = True

' Adds columns on the worksheet to cells in the listbox
QuerySearch.DisplayResults.Column(0, lngItem) = sht.Cells(lngRow, 13)
QuerySearch.DisplayResults.Column(1, lngItem) = sht.Cells(lngRow, 4)
QuerySearch.DisplayResults.Column(2, lngItem) = sht.Cells(lngRow, 7)
QuerySearch.DisplayResults.Column(3, lngItem) = sht.Cells(lngRow, 8)
End If
Next

Set sht = Nothing

End Sub

[Ecniv]

You are using excel as a database?
Why not use a database??? Like Access? (or Sql server or something else)?

Are you going through a userform?
What is QuerySearch?

[Aaron Bean]

Hi Query Search is the name of the user form, I need a userform to allow the user to input the value they would like to search and then it returns all entries (rows) in the spreadsheet with this value. I now need to be able to select an entry in the list box and go to that row on the spreadsheet. Thanks, Aaron

[Ecniv]

  activesheet.cells(clng(lst.column(0)),1).select

Or something like that.

lst is your list box.
activesheet can be replaced with your variable that points to the data sheet.

[Aaron Bean]

I'm still not sure how this works, how does this put the row number into the listbox on my userform?

[Aaron Bean]

I'm still not sure how this works, how does this put the row number into the listbox on my userform?

[Ecniv]

Ok... what I mean is this.

Your list box has the row that you want to look at in the hidden column (first one) so when your user selects the item your code can find that row and read data.

Now, the previously posted example would make the cell have the selection, ie move to display it in the sheet. I was using it as an example so you can tailor the code to retrieve data and display on text fields on the form or you can show that record on the spreadsheet.

Alternative is to use the autofilter of the spreadsheet and set the filters appropriately to filter to those that you want to display.

Post up your code when you have had a play if it doesn't work.

[Aaron Bean]

Thanks for your help. All I can get it to do now is return 1 instead of the row number (I think because there is 1 row in the selection). Could you have a look at the code?

Query search is the name of the userform and displayresults the name of the listbox. I have tried RowNum = Cells.Row but this doesnt work. Any ideas?


Private Sub Search_Click()

Dim sht As Worksheet
Dim lngMaxRows As Long, lngRow As Long 'lngMaxRows = total rows
Dim lngItem As Long
Dim SearchTextBox As Long

Sheets("Month").Select
Set sht = ActiveSheet
QuerySearch.DisplayResults.Clear
lastRow = WorksheetFunction.CountA(Range("A:A"))
'lngMaxRows = Cells(65535, 13).End(xlUp).Row

For lngRow = 2 To lastRow
If QuerySearch.SearchTextBox.Text = sht.Cells(lngRow, 13).Value Then
DisplayResults.AddItem ""
lngItem = QuerySearch.DisplayResults.ListCount - 1

RowNum = Cells.Row
QuerySearch.DisplayResults.Column(0, lngItem) = RowNum
QuerySearch.DisplayResults.Column(1, lngItem) = sht.Cells(lngRow, 13)
QuerySearch.DisplayResults.Column(2, lngItem) = sht.Cells(lngRow, 3)
QuerySearch.DisplayResults.Column(3, lngItem) = sht.Cells(lngRow, 4)
QuerySearch.DisplayResults.Column(4, lngItem) = sht.Cells(lngRow, 16)
'ActiveSheet.Cells(CLng(DisplayResults.Column(0)), 1).Select
End If
Next

Set sht = Nothing

End Sub

[JustinLabenne]

After reading this for a bit, I just am not sure how it is supposed to work.

So the user types a value in a userform textbox, the code should look for that value (where? anywhere in the sheet, or will it be found in column A only?)

I am not sure what details are being returned into this listbox and what or how the user can edit these details.

Do you just want to replace the values being searched for with different values?
If you could attach a sample of at least the worksheet and how it's laid out, that would help out a lot, I just can't visualize what the listbox is being filled with, and how the stuff is being edited.

[Aaron Bean]

Hi, yes the user types in a value they want to search into the search text box and it searches the worksheet for matches. When it finds a match it returns it to the first column in the listbox along with a few other details (i.e. colum 3, 4 and 16). I just need to find a way of adding the row number to the first column of the list box of results so I can then use that to go to this option once they have selected it. I cant seem to attach a workbook do you know how?

[JustinLabenne]

To attach it, scroll down when replying to a post, Manage-Attachments and attach a zip file of your files.

That did explain a bit better, but if you can attach something that would help.