Another WorksheetFunction.Match problem (SOLVED)

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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Jay Petrulis

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
Hi,

I'm not sure if this will work, but you can try to coerce the number to a long integer or a double and then do your comparison.

CustomerSearch = Application.Match(CLng(TxtSearch),Range("Phone1"),0)

or

CustomerSearch = Application.Match(CDbl(TxtSearch),Range("Phone1"),0)

Note that I changed the function to Application.Match. The reason is that using MATCH in VBA is quirky. It may work for you at some times and not at others, though for no good reason. Application.Match appears to be much more stable than Application.WorksheetFunction.Match or WorksheetFunction.Match and I would recommend that you use it this way.

If this fails, please make sure that the lookup range "Phone1" is working correctly -- that you are referencing the range you need. If it is defined using .Offset or such, it may be that you are in the wrong column.

One thing that you would likely need to do is have error handling when you don't find an exact match.

There are other ways that may allow you to search the same thing if Match fails. A dropdown box using the range as possible input would certainly ensure that only those entries in the range are available.
 

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
Using the CDbl worked! Since I've never used that before, I have a few questions.

A) By using the CDbl function, did it turn the data into a number?

B) Why wouldn't TxtSearch.Value * 1 do the same thing?

C) In what instances would you most likely use this?

As always, your help is VERY appreciated.
 

Jay Petrulis

MrExcel MVP
Joined
Mar 17, 2002
Messages
2,040
On 2002-10-09 20:12, phantom1975 wrote:
Using the CDbl worked! Since I've never used that before, I have a few questions.

A) By using the CDbl function, did it turn the data into a number?

B) Why wouldn't TxtSearch.Value * 1 do the same thing?

C) In what instances would you most likely use this?

As always, your help is VERY appreciated.

Hi,

A) No. The TxtSearch variable is still a string. What you have done is change the test argument to a number. There is a difference for sure.

Think of the CDbl as being "Change to Double" but the way it is used here, you are changing the test parameter, not the variable. You *can* change the variable by doing something like

TxtSearch = CDbl(TxtSearch)

Of course you must declare and dimension the variable correctly.

B) I don't know. This definitely works on the worksheet, and I am sure there are many instances where it works, but I am stumped here.

C) The same as you. I would've tried this after typing in something like you have done. In general I add zero rather than multiply by one, but that is just a personal preference. Your approach often works. Here it didn't, but I cannot fully explain why.

So, I would likely have done something similar as you have done, then when it didn't work, I would've gone to Plan B. :)

You cay also try Val(TxtSearch) although you should read up on what it will return exactly.
 

Forum statistics

Threads
1,147,748
Messages
5,742,971
Members
423,769
Latest member
LongToast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top