phantom1975
MrExcel MVP
- Joined
- Jun 3, 2002
- Messages
- 3,962
I have a worksheet that acts as a database. Column A has their first name, Column B their last name, etc. I am trying to create a UserForm that will work as a search engine. There are 2 different ways data can be searched; either by last name or by phone number.
Doing the search by last name works perfectly, however, doing the search by telephone number does not. This is how the code in the UserForm looks:
Private Sub BtnSearch_Click()
Dim CustomerSearch as Variant
Dim SearchValue as Variant
Select Case BtnSearch.Caption
Case "Click HERE to Search by Phone Number"
CustomerSearch = Application.WorksheetFunction.Match(TxtSearch.Value,Range("Phone1"),0)
Case "Click HERE to Search by Last Name"
CustomerSearch = Application.WorksheetFunction.Match(TxtSearch.Value, Range("Last"),0)
End Select
SearchValue = Sheets("Data Sheet").Cells((CustomerSearch) + 2, 3).Value
MsgBox "Is " & SearchValue & " the correct person?", vbYesNo, "Search Results"
End Sub
TxtSearch is the TextBox on the UserForm where the data you want searched is typed. Phone1 is a range of cells that contains phone numbers in it. When I try the search using a value in Phone1, I get the following error:
Run-time error '1004':
Unable to get the Match property of the WorksheetFunction class
I don't get this error when I do a search by last name. I thought that it might have something to do with the value in the TextBox being considered text while the data in Phone1 was considered a number. I even tried multiplying TxtSearch.Value by 1 to convert it to a number, and it still didn't work. I have also turned off all of the formatting within the range of Phone1 as well. Any suggestions?
_________________
Pass on what you have learned. Support this great website by clicking on the sponsor's ads!.
This message was edited by phantom1975 on 2002-10-09 20:53
Doing the search by last name works perfectly, however, doing the search by telephone number does not. This is how the code in the UserForm looks:
Private Sub BtnSearch_Click()
Dim CustomerSearch as Variant
Dim SearchValue as Variant
Select Case BtnSearch.Caption
Case "Click HERE to Search by Phone Number"
CustomerSearch = Application.WorksheetFunction.Match(TxtSearch.Value,Range("Phone1"),0)
Case "Click HERE to Search by Last Name"
CustomerSearch = Application.WorksheetFunction.Match(TxtSearch.Value, Range("Last"),0)
End Select
SearchValue = Sheets("Data Sheet").Cells((CustomerSearch) + 2, 3).Value
MsgBox "Is " & SearchValue & " the correct person?", vbYesNo, "Search Results"
End Sub
TxtSearch is the TextBox on the UserForm where the data you want searched is typed. Phone1 is a range of cells that contains phone numbers in it. When I try the search using a value in Phone1, I get the following error:
Run-time error '1004':
Unable to get the Match property of the WorksheetFunction class
I don't get this error when I do a search by last name. I thought that it might have something to do with the value in the TextBox being considered text while the data in Phone1 was considered a number. I even tried multiplying TxtSearch.Value by 1 to convert it to a number, and it still didn't work. I have also turned off all of the formatting within the range of Phone1 as well. Any suggestions?
_________________
Pass on what you have learned. Support this great website by clicking on the sponsor's ads!.
This message was edited by phantom1975 on 2002-10-09 20:53