Searching worksheet for clients name and info
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Searching worksheet for clients name and info
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.
I have attached a sample workbook.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Searching worksheet for clients name and info
Can you give an example of a search that doesn't return the desired results?
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 392
- Joined: 25 Jan 2010, 12:21
Re: Searching worksheet for clients name and info
If it is important to see your file, could you attach an uprotected one or provide the password that it prompts us for?
Steve
Steve
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Searching worksheet for clients name and info
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.
Added: Oh I see - there's a password on the structure.
Best wishes,
Hans
Hans
-
- 3StarLounger
- Posts: 392
- Joined: 25 Jan 2010, 12:21
Re: Searching worksheet for clients name and info
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
Steve
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Searching worksheet for clients name and info
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.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Searching worksheet for clients name and info
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
You do not have the required permissions to view the files attached to this post.
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Searching worksheet for clients name and info
I was also able to Save As Excel 97-2003 with no problems?????
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Searching worksheet for clients name and info
If you want to find the exacty name you entered, use
(I changed Cleint to Client and added LookAt:=xlWhole)
Code: Select all
Set rng = Range("A6:A1000").Find(What:=InputBox("Enter client name below, then select OK.", "Client Information Search"), LookAt:=xlWhole)
Best wishes,
Hans
Hans
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Searching worksheet for clients name and info
I can save your Sample2.xlsm in Excel 97-2003 format too. Perhaps the protection was the problem...
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Searching worksheet for clients name and info
Thank You Hans
and thank you for the "Hans spell checker"
and thank you for the "Hans spell checker"
-
- 3StarLounger
- Posts: 392
- Joined: 25 Jan 2010, 12:21
Re: Searching worksheet for clients name and info
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
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
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Searching worksheet for clients name and info
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
I think I will stay with Lookat:=xlWhole for an exact match only.
Again thank you Steve
-
- 3StarLounger
- Posts: 392
- Joined: 25 Jan 2010, 12:21
Re: Searching worksheet for clients name and info
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
Steve
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Searching worksheet for clients name and info
Hi Steve
I see what you mean, what would it take to loop as you suggested
I see what you mean, what would it take to loop as you suggested
-
- Administrator
- Posts: 78658
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Searching worksheet for clients name and info
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
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 1868
- Joined: 25 Jan 2010, 14:00
- Location: Conroe, Texas
Re: Searching worksheet for clients name and info
Hi Hans
I tried the code you presented, very interesting.
Thank you
I tried the code you presented, very interesting.
Thank you