Search and Edit code

[Aaron Bean]

Does ayone know how I can create a search function which seaches a worksheet with load of different entries (1 entry per row) then returns possible search results in a userform (i.e if you search by owner the owner may own more than one account so you would need to retur all possible results)

I then need the user to select which is the right one and then be able to edit it if possible. I guess this would involve telling the macro which row the entry is on to be able to retrieve all the data already inputted. At present each row has a unique reference number.

If you can help me with this you are a genius! Thanks

[Ecniv]

Text box to enter the search.
Button to run the code.
Listbox for the display matches.
Code to :
- Loop through sheet and put in matches to the listbox (2 columns - first hidden holds row number)
- double click code on list box to use the sheets 'goto' command. (.select)

[Aaron Bean]

Thanks for your help, I am still a bit stuck on the code that loops through the sheet and puts matches into the listbox. To find a reference number that the user has input I need to search through column D do you know how I would do this?

Thanks again, Aaron

[Ecniv]

I think this is the right attachment...here

Should be an excel sheet to load values into a list box (I think). If not I will edit and add the right one... I posted it up a while back.


Edit:
Nope, this is the right one... here
Demonstrates how to get values into a listbox into multiple columns...

[Aaron Bean]

Thats a great help thanks and nearly exactly what I am looking for, however, I just need to add a bit of code before that to allow the user to search for an entry i.e. test1 in your example, so that it then only returns the test1 record in the userform listbox. Or if there are 2 named test1, it returns them both. Do you know how I would do this?

Thanks again for your help - you are saving me from going mad trying to do this! ;-)

[Ecniv]

Since you are meant to be doing it..

You need to use an IF statement to check whether it is the same as the users input. Add to the list box if it matches. Keep looking until you reach the bottom of the spreadsheet/datalist.
OR
You need to use the sheet/range .find (read help file) to see if it returns a range object (ie found something) store the address of the first found one so when you loop back you know you have it already... If none found then the range is nothing.

Something along those lines. Fairly simple if you use the top-most option as this loops through all and checks. You can always put in the second one later if you have time/want to experiment.

[Aaron Bean]

Hi, sorry to bother you again. I cant seem to get the if statement to work as I am not to sure what I am looking up. Do you know why the code below isn't working? SearchAmoutn is the textbox I am looking for. I am not too sure what to tell it to look up? What is lngItem? I am trying to see if the value in the text box is in column 13 or not?

Thanks so much for your help.


Dim sht As Worksheet
Dim lngMaxRows As Long, lngRow As Long
Dim lngItem As Long

On Error Resume Next

Set sht = ActiveSheet
DisplayResults.Clear
lngMaxRows = sht.Cells(65535, 13).End(xlUp).Row

For lngRow = 1 To lngMaxRows
If SearchAmount.Value = sht.Cells(lngItem, 13) Then
DisplayResults.AddItem ""
lngItem = DisplayResults.ListCount - 1

DisplayResults.Column(0, lngItem) = sht.Cells(lngRow, 13)
DisplayResults.Column(1, lngItem) = sht.Cells(lngRow, 4)
DisplayResults.Column(2, lngItem) = sht.Cells(lngRow, 7)
DisplayResults.Column(3, lngItem) = sht.Cells(lngRow, 8)
End If
Next
Set sht = Nothing

[si_the_geek]

I've only looked briefly, but shouldn't lngItem in the If statement be "lngRow" instead?

ie:

If SearchAmount.Value = sht.Cells(lngRow, 13) Then

lngItem isn't actually set to anything at that point, so is meaningless

[Aaron Bean]

I originally had lngItem as lngRow but that didn't work so I tried it another way. I have just tried putting lngRow back in and it still doesn't work. Any ideas as I'm running out of them!

[si_the_geek]

In that case the If isn't finding any matches. Adding .Value to the cell should help, eg:

If SearchAmount.Value = sht.Cells(lngRow, 13).Value Then

If this still doesn't work, it could be that there are no matching cells, or the data types aren't the same. If there are matching cells, you will need to do some debugging to find out why they aren't being recognised.

[Aaron Bean]

I have just tried that and it still does nothing. Now when I click on search nothing happens and nothing is returned to the listbox. Do you know if the If is in the right place. I also don't understand what this bit of code does....

DisplayResults.AddItem ""
lngItem = DisplayResults.ListCount - 1

As I don't know how .AddItem "" would make it add the row details I would like and I don't know where lngItem comes from? Any ideas?

[si_the_geek]

The .Additem adds a row to the DisplayResults list, and lngItem is set to the row number (so in the following lines, the text can be added to the row).

The If is in the correct place, it just isn't finding any matching rows. Are you sure that the value you are searching for is exactly the same as the contents of one of those cells? (the entire cell, not just part of it).

[Aaron Bean]

The value is definitely the same as the contents of the entire cell. I just cant seem to get this working. It works fine without the If statement... i.e. it returns everythin in the worksheet but as soon as I add the If it returns nothing. Here is the the code again. If you can help me solve this I will be eternally grateful! ;-)

Private Sub Search_Click()

Dim lngMaxRows As Long, lngRow As Long 'lngMaxRows = total rows
Dim lngItem As Long

On Error Resume Next

Sheets("Month").Activate
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.SearchAmount.Value = Cells(lngRow, 13).Value Then
DisplayResults.AddItem ""
lngItem = QuerySearch.DisplayResults.ListCount

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

End Sub

[si_the_geek]

You've now added a few potential errors by removing the sht variable
It would recommend having the code like this:

Private Sub Search_Click()
 
Dim sht As Worksheet
Dim lngMaxRows As Long, lngRow As Long 'lngMaxRows = total rows
Dim lngItem As Long
 
  'On Error Resume Next   'this is VERY bad while writing code (it may be hiding the problem)
 
  Sheets("Month").Activate
  Set sht = ActiveSheet
  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.SearchAmount.Value = sht.Cells(lngRow, 13).Value Then
      DisplayResults.AddItem ""
      lngItem = QuerySearch.DisplayResults.ListCount
 
      ' 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

I would strongly recommend removing the line "On error resume next", as this will just hide any errors from you. While you are writing code, this is far worse than having no error handling at all.


In order to find out why the If isn't working (and it is only the If), you will need to do some debugging.

To do this, put a breakpoint on the If line (click on that line of code with your mouse and press F9), then run the code. It will stop at that line, and you can see what the values of the conditions in the If statement are. You can do this by either moving the mouse cursor over the conditions (eg: sht.Cells(lngRow, 13).Value) or by adding a watch on these values.

[Aaron Bean]

Thanks for all your help. I have tried this and added a breakpoint, it seems to have the right values in i.e. QuerySearch.SearchAmount.Value does = the value I typed in and the lngRow part of this.... sht.Cells(lngRow, 13).Value does have the value of 1. I then put the breakpoint a line down and tried it again and nothing happens, no error message or anything, just nothing?

I'm not sure I have got the right code to tell it what to do with the if Statement when it has checked it. What do you think?

[si_the_geek]

The rest of code looks fine, it is only the If that is the problem.

The reason that "nothing happens" when you moved the breakpoint is that the If did not work.

To find out exactly why, add the following as watches:

sht.Cells(lngRow, 13).Value
QuerySearch.SearchAmount.Value
QuerySearch.SearchAmount.Value = sht.Cells(lngRow, 13).Value

..and try running it again (with the breakpoint on the If line).

Then post here exactly what is in the watch window when the values should match (preferably as a screenshot).

[Aaron Bean]

Apologies... I dont seem to be able to paste a screen shot in..

Here are the results for the watches...

sht.Cells(lngRow, 13).Value value = "200 type = variant/string
QuerySearch.SearchAmount.Value Value = False type = variant Boolean
QuerySearch.SearchAmount.Value = sht.Cells(lngRow, 13).Value value = "PAYMENT AMOUNT" type = Variant/string

Context is same for them all QuerySearch.Search_Click

Thanks, Aaron

[IMG]Desktop\screen.bmp[/IMG]

[si_the_geek]

I forgot to mention previously that you can press F5 to continue running the code when it has reached a breakpoint (or F8 to just run the next line), in this situation that would mean that you get to see the values in the next row of the spreadsheet.

In terms of attaching a screenshot, you need to add the screenshot as an attachment to your post. To do this, click on the "Manage Attachments" button (one of the options below where you type), then click on browse and select the file. After that press "Upload", and the image will be automatically added to the bottom of your post.


I presume you mis-pasted the last two values there, as the final row should be the boolean Unfortunately this hasn't highlighted the issue, as we cannot see a numerical row in the spreadsheet (which is likely to be the problem).

Anyway, as I took so long to answer this I will post a method which should work, but it is not very efficient - we can improve on the speed (and size!) of this line if you post a screenshot where the two values appear to be equal.

If UCase(Trim(QuerySearch.SearchAmount.Value)) = UCase(Trim(CStr(sht.Cells(lngRow, 13).Value))) Then