Searching worksheet for clients name and info

[ABabeNChrist]

I have a worksheet that is used as a client log sheet. This sheet is used throughout the year to keep records of clients name, address, inspection fees and so on. By the end of the year this log sheet could hold up to 500+ clients. I’m using a piece of code that will help me search for a specific clients name and info within this log sheet. The problem I have encountered is the code that I am using may turn up a search that is similar. Is it possible to have the search be more accurate in its search?
I have attached a sample workbook.

Sample.xlsm

[HansV]

Can you give an example of a search that doesn't return the desired results?

[sdckapr]

If it is important to see your file, could you attach an uprotected one or provide the password that it prompts us for?

Steve

[HansV]

I can view the workbook and code without being prompted for a password in Excel 2007...

Added: Oh I see - there's a password on the structure.

[sdckapr]

I don't have 2007. I have 2000 at home and 2002 at work. They must be converted and the converter requires the password...

Steve

[HansV]

There must be something amiss with the workbook; I tried to save it as an Excel 97-2003 workbook from Excel 2007 but got a series of error messages about irrepairable damage. If I try "Open and Repair", Excel 2007 crashes. That's probably why the converter cannot handle it.

[ABabeNChrist]

Try typing a new entry named “Gary ro” at the next available row. Then do a search for that name. This is how it appears when I try. I know they are very close / similar to a couple others, I was hoping to be a little more exact in the search. Better yet here’s a unprotected workbook xlsm

Sample 2.xlsm

[ABabeNChrist]

I was also able to Save As Excel 97-2003 with no problems?????

Sample 3.xls

[HansV]

If you want to find the exacty name you entered, use

Code: Select all

    Set rng = Range("A6:A1000").Find(What:=InputBox("Enter client name below, then select OK.", "Client Information Search"), LookAt:=xlWhole)

(I changed Cleint to Client and added LookAt:=xlWhole)

[HansV]

I can save your Sample2.xlsm in Excel 97-2003 format too. Perhaps the protection was the problem...

[ABabeNChrist]

Thank You Hans
and thank you for the "Hans spell checker"

[sdckapr]

Since you have Gary Ro, Gary Root, and Gary Roo and they all match "Gary Ro" it is not surprising you might not get the exact match. Find just finds the first one...

You can use the parameter Lookat:=xlWhole to find exact matches or loop through the find to get all the matches and have the user pick one....

Steve

[ABabeNChrist]

Thank you Steve, the looping sounded interesting, but may get to confusing.
I think I will stay with Lookat:=xlWhole for an exact match only.
Again thank you Steve

[sdckapr]

Exact match may not be very user friendly. For example entering in "Gary" will not find anyone. You will have to enter Gary Roo, Gary Ro or Gary Root to get a match....

Steve

[ABabeNChrist]

Hi Steve
I see what you mean, what would it take to loop as you suggested

[HansV]

Here is a compromise: if there is no exact match, the code will return the first partial match. Note that I removed the superfluous error handling.

Code: Select all

Private Sub CommandButton2_Click()
    Dim rng As Range
    Dim lngrng As Long
    Dim strSearch As String
    Dim strMessage As String
    Dim headname As String

    strSearch = InputBox("Enter client name below, then select OK.", "Client Information Search")
    Set rng = Range("A6:A1000").Find(What:=strSearch, LookAt:=xlWhole, MatchCase:=False)
    If rng Is Nothing Then
        Set rng = Range("A6:A1000").Find(What:=strSearch, LookAt:=xlPart, MatchCase:=False)
        strMessage = "There was no exact match. Here is the first partial match:" & vbCrLf & vbCrLf
    End If
    If Not rng Is Nothing Then
        For lngrng = 0 To 6   'This will show the next 6 cells of the row
            headname = Cells(5, lngrng + 1).Value
            strMessage = strMessage & headname & " " & rng.Offset(, lngrng).Value & vbCrLf
        Next lngrng
        MsgBox strMessage, , "Here are your search results"
    Else
        MsgBox "Try Again", , "No Match Found!"
    End If
End Sub

[ABabeNChrist]

Hi Hans
I tried the code you presented, very interesting.
Thank you