Using IsError with Match

I have a list box from which a user may select a client.  My code then looks 
through a list of employees to find the ones belonging to that client.  For 
various reasons, it is possible to select a client that might not be 
represented in the list of employees (it's necessary, I assure you).  In case 
the user selects a client that isn't in the employee list, I wanted to catch 
this and avoid an error message.  The code that is supposed to do this is 
below.

If IsError(Application.WorksheetFunction.Match(ClientNum, ClientEEs, 0)) = 
True Then
' Do stuff
End If

However, I still get the "Unable to get the Match property of the 
WorksheetFunction class" error message.  I don't really want to use On Error 
Resume Next because, if the user selects a client not in the list of 
employees, I've got a whole section of code I want to skip over.

How can I make this work so that it sees the error, sets a couple of 
variables equal to 0, then continues at a different point in the code?

Thanks for any help.
0
Utf
3/5/2010 4:16:08 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
943 Views

Similar Articles

[PageSpeed] 16

That should work. It can be shortened to

If IsError(Application.Match(ClientNum, ClientEEs, 0)) Then
' Do stuff
End If

but that is essentially the same.

You could use Onerror like so

On Error Resume Next
idx =Application.Match(ClientNum, ClientEEs, 0))
On Error Goto 0
If idx > 0 Then
'Do stuff
End If


-- 

HTH

Bob

"Luke" <Luke@discussions.microsoft.com> wrote in message 
news:A35CFE70-250B-47B6-8BB3-3AA138031BA3@microsoft.com...
>I have a list box from which a user may select a client.  My code then 
>looks
> through a list of employees to find the ones belonging to that client. 
> For
> various reasons, it is possible to select a client that might not be
> represented in the list of employees (it's necessary, I assure you).  In 
> case
> the user selects a client that isn't in the employee list, I wanted to 
> catch
> this and avoid an error message.  The code that is supposed to do this is
> below.
>
> If IsError(Application.WorksheetFunction.Match(ClientNum, ClientEEs, 0)) =
> True Then
> ' Do stuff
> End If
>
> However, I still get the "Unable to get the Match property of the
> WorksheetFunction class" error message.  I don't really want to use On 
> Error
> Resume Next because, if the user selects a client not in the list of
> employees, I've got a whole section of code I want to skip over.
>
> How can I make this work so that it sees the error, sets a couple of
> variables equal to 0, then continues at a different point in the code?
>
> Thanks for any help. 


0
Bob
3/5/2010 4:29:08 PM
Drop the .worksheetfunction portion:

if IsError(Application.Match(....



Luke wrote:
> 
> I have a list box from which a user may select a client.  My code then looks
> through a list of employees to find the ones belonging to that client.  For
> various reasons, it is possible to select a client that might not be
> represented in the list of employees (it's necessary, I assure you).  In case
> the user selects a client that isn't in the employee list, I wanted to catch
> this and avoid an error message.  The code that is supposed to do this is
> below.
> 
> If IsError(Application.WorksheetFunction.Match(ClientNum, ClientEEs, 0)) =
> True Then
> ' Do stuff
> End If
> 
> However, I still get the "Unable to get the Match property of the
> WorksheetFunction class" error message.  I don't really want to use On Error
> Resume Next because, if the user selects a client not in the list of
> employees, I've got a whole section of code I want to skip over.
> 
> How can I make this work so that it sees the error, sets a couple of
> variables equal to 0, then continues at a different point in the code?
> 
> Thanks for any help.

-- 

Dave Peterson
0
Dave
3/5/2010 4:45:01 PM
Worked like a charm.  Thank you very much.

If you don't mind, though, can you tell me why that made a difference?

Thanks.

"Dave Peterson" wrote:

> Drop the .worksheetfunction portion:
> 
> if IsError(Application.Match(....
> 
> 
> 
> Luke wrote:
> > 
> > I have a list box from which a user may select a client.  My code then looks
> > through a list of employees to find the ones belonging to that client.  For
> > various reasons, it is possible to select a client that might not be
> > represented in the list of employees (it's necessary, I assure you).  In case
> > the user selects a client that isn't in the employee list, I wanted to catch
> > this and avoid an error message.  The code that is supposed to do this is
> > below.
> > 
> > If IsError(Application.WorksheetFunction.Match(ClientNum, ClientEEs, 0)) =
> > True Then
> > ' Do stuff
> > End If
> > 
> > However, I still get the "Unable to get the Match property of the
> > WorksheetFunction class" error message.  I don't really want to use On Error
> > Resume Next because, if the user selects a client not in the list of
> > employees, I've got a whole section of code I want to skip over.
> > 
> > How can I make this work so that it sees the error, sets a couple of
> > variables equal to 0, then continues at a different point in the code?
> > 
> > Thanks for any help.
> 
> -- 
> 
> Dave Peterson
> .
> 
0
Utf
3/5/2010 5:34:01 PM
Just the way Microsoft implemented it (same with =vlookup(), too).

dim res as variant
on error resume next
res = application.worksheetfunction.match(...)
if err.number <> 0 then
   'an error
   err.clear
   res = "error"
end if
on error goto 0

Would be an alternative way of using application.worksheetfunction or just
worksheetfunction.



Luke wrote:
> 
> Worked like a charm.  Thank you very much.
> 
> If you don't mind, though, can you tell me why that made a difference?
> 
> Thanks.
> 
> "Dave Peterson" wrote:
> 
> > Drop the .worksheetfunction portion:
> >
> > if IsError(Application.Match(....
> >
> >
> >
> > Luke wrote:
> > >
> > > I have a list box from which a user may select a client.  My code then looks
> > > through a list of employees to find the ones belonging to that client.  For
> > > various reasons, it is possible to select a client that might not be
> > > represented in the list of employees (it's necessary, I assure you).  In case
> > > the user selects a client that isn't in the employee list, I wanted to catch
> > > this and avoid an error message.  The code that is supposed to do this is
> > > below.
> > >
> > > If IsError(Application.WorksheetFunction.Match(ClientNum, ClientEEs, 0)) =
> > > True Then
> > > ' Do stuff
> > > End If
> > >
> > > However, I still get the "Unable to get the Match property of the
> > > WorksheetFunction class" error message.  I don't really want to use On Error
> > > Resume Next because, if the user selects a client not in the list of
> > > employees, I've got a whole section of code I want to skip over.
> > >
> > > How can I make this work so that it sees the error, sets a couple of
> > > variables equal to 0, then continues at a different point in the code?
> > >
> > > Thanks for any help.
> >
> > --
> >
> > Dave Peterson
> > .
> >

-- 

Dave Peterson
0
Dave
3/5/2010 5:55:42 PM
Reply:

Similar Artilces:

Learning to Use Word 2007 as a Blind Person
I'm transitioning from Word 2000 to 2007 and would like to know if there's a way for a blind user like me using the screen-reading program called JAWS 10 )from Freedom Scientific) to learn Word 2007 quickly and thoroughly. Thank you for your feedback! I don't mean to be dismissive, but sighted people have a lot of trouble making the transition from the older Word interface to that of 2007. I don't know anything about the screen reading program, but frankly I don't see it helping you learn 2007 *quickly*. It took me over a year to become comfortable with 200...

How do I run a rule by using a toolbar icon?
I have Outlook 2007 and have a rule set up to delte spam by detecting certain words in the spam. Here is what someone gave me but it does not work: Sub RunRuleDeleteSpam() Dim oNS As Outlook.NameSpace Dim oStore As Outlook.Store Dim colRules As Outlook.Rules Dim oRule as Outlook.Rule Set oNS = Application.GetNameSpace("MAPI") Set oStore = oNS.DefaultStore Set colRules = oStore.Rules Set oRule = colRules.Item("Delete Spam") oRule.Execute End Sub If I run it it highlights the set oRule line and stops, I guess. I know n...

Allow work order deposits to be paid for using store credits
---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=ed16e74f-2d3a-4689-9444-cfa2cb09e82a&dg=microsoft.public.pos ...

Using E2k7 Mgmt Shell to create resource (rooms, equip) mailboxes
Good Day, I've got an OU with disabled "users" that I would like to create room mailboxes for. I've successfully created regular user mailboxes using the same cmdlet but am unable to figure out the correct syntax and variable changes for creating room mailboxes. The cmdlet is as follows: get-user -organizationalUnit "E2k7 - Test Conf Rooms" | where-object{$_.RecipientType -eq "User"} | Enable-Mailbox -Database "MBXservername\mailbox database" | get-mailbox | select name,windowsemailaddress,database When run on an OU of regular users, thi...

Using a shared calendar in Outlook 2003
The shared calendar was setup in Outlook 2000 on another machine. Am I able to gain access to the calendar or no? I tried to install the net folders component but it seems it's not available for 2003, is this true? Thanks for your help. Yes, net folders has been dropped since Outlook 2002. There are a lot of third party tools that can do it when you don't connect to an Exchange server. Share just the Calendar; http://tinyurl.com/3p6xm Share all Outlook folders http://tinyurl.com/49lzg Also take a look here http://www.slipstick.com/outlook/share.htm -- Robert Sparnaaij [M...

Distribution list using Address Card Info emailed to another perso
1. I chose Address Card in view 2. Pulled up Distribution list selection 3. Clicked on top name and holding Ctrl button down selected entire list 4. On address line I right clicked and chose "Select All" Question: how do I get all of the info on Address cards in the list over to an email to send? That's not the way to send Contact information. Why wouldn't you send the Contact as an attachment or a vCard? -- Russ Valentine "Jean Rodwick" <JeanRodwick@discussions.microsoft.com> wrote in message news:3421799A-DAD3-4A9D-9CF9-DFEC60903398@microso...

Use of "OnSubmit"
I would like to execute a function when the user clicks on the "SUBMIT" button in a PHP/HTML form. I think I should use "OnSubmit" event. How to use OnSubmit in a PHP form? Is following syntax correct? <form method="post" action="G_Training.php" name="G-User-Details" style="border: thin double #FF0000" title="User Details" id="New Form" onsubmit="checkFormBeforeSubmit"> Submitted via EggHeadCafe - Software Developer Portal of Choice A Down and Dirty FileSystem Object Page Counter http://...

Monthly Reports use closed accounts
I just upgraded to Money Plus Premium and was trying various things to make sure it all worked. I can set up most reports to include the Accounts and Investments I want but the Monthly Reports won't allow me to do that. When I run them that report includes accounts I have closed years ago and investments I have sold. The result is that the section that shows my best and worst investments for the month is pretty useless because it lists mostly things I have no interest in. In fact I suppost all the other information in the monthly report is equally useless since it is working on the ...

how use dlookup to update table
hi everybody I come from china. Firstly,I'm sorry. My english is not good. I have a question about dlookup I have two tables. One table (table1)only have two fields "file name" and "file ID". Another table(table2) also include field "file ID" and "file name". table1 have many records. I want to accord record from table1 to update table2 when table2.file name=table1.file name. I design a form to input table2, there is a combobox in the form that I hope its list from table11.file name,when I choice a file name another textbox value will ...

Zip files using makeDirZip.exe
Hi All, I need to zip subfolders which resides under the parent folders which are 201003,201004,201005(for each month). Under the parent folder.example 201003->Titles->then many sub folders for each day of the month,I need to use MakeDirZip.exe to only zip sub folders for each day under the root folder(201003) which is 2 months old from this month. Please shed some light on it. I am trying to use, C:\test>makeDirZip.exe i=\\<filer>\c$\cdc dubug='yes' purge='no' which is zipping every single date folder no matter whether this month or 2 months ol...

Implementing an html functionality using MFC application
Sir, I have a web site which has mainly a TextArea and an OK button which sends the message entered in the text area to the site. Can i create an MFC application by which when i push a button the same message should be send to the desired site.. Yes, it is possible using wininet functions. Can you post your webpage? -- Vipin Aravind Microsoft MVP "rocky" <kirenmukherjee@gmail.com> wrote in message news:1137390403.945426.143660@g49g2000cwa.googlegroups.com... > Sir, > I have a web site which has mainly a TextArea and an OK button which > sends the message entered ...

How to use global function
Hi I think I am asking a silly question,but plz help me.I am defining a function in global space and I want to use it in a class.but error is coming saying that this function is undeclared identifier.How to do that? <bhattacharjeesoft@gmail.com> ha scritto nel messaggio news:1186204361.724267.84940@x40g2000prg.googlegroups.com... > I think I am asking a silly question,but plz help me.I am defining a > function in global space and I want to use it in a class.but error is > coming saying that this function is undeclared identifier.How to do > that? Could you please pos...

Matching cells by content then cell fill with color
Thanks to JEM, I am using this routine to color three consecutive cell a specific color, in this case red: Public Sub ThreeCellsRed() ActiveCell.Resize(1,3).Interior.ColorIndex = 3 End Sub What I need now is a way for the routine to continue to find all th similar cells, let's say for sake of disc they are people's names, s when I execute the above on my name, mrh, I want it to continue in th worksheet and find all exact matches and color those same cells red. Another thought, say my name (MRH) is in "A1" and it is also in "D1". But in "D1" I use "...

Using Excel spreadsheet online
I have built a spreadsheet that allows me to track inventory and revenue for products that i have a team of people selling. I sthere any way that I can post this spreadsheet online so that my salespeople can update it from a browser? I know a bit about Excel but I don't know diddly about what might be needed on the server end or how it would save the data. Any sugestions? "Monkeysuit Man" <monkeysuitman@monkeysuit.com> wrote in message news:18m7mvsiqkonfcmlpfl125rc5s3h0gfah8@4ax.com... > I have built a spreadsheet that allows me to track inventory and > revenue fo...

High CPU when using New Tab Option in IE7
When I specify 'Open Pop-Ups in New Tab' in IE7, some pages (Invoice fo example) sometimes takes minutes to close, even when just 'X'ing the window. Nothing is being saved, there is Jscript on the Save event but I tested with an alert and the JS is not being executed. The client CPU drives to 50-100% while the window goes blank. Switching back to windowed mode, or IE6, seems to fix the problem. Anyone heard of this or having similar problems? -- Thanx Grizz I was having the same issue with loading pages in CRM. Speaking with MS Tech Support the other day, they stated th...

Using Outlook BCM on a server
Can I use Outlook BCM in a server (the contact database residing on server) and access that information, add to it seamlessly, etc. Also, can I see all notes (opportunity notes, time/date stamping) in a single screen? I think I posted it in a wrong place. "Yuri" wrote: > Can I use Outlook BCM in a server (the contact database residing on server) > and > access that information, add to it seamlessly, etc. > Also, can I see all notes (opportunity notes, time/date stamping) in a > single screen? "Yuri" <Yuri@discussions.microsoft....

reminders on outlook 2007 using two calendars
we are using Outlook 2007. We each have a personal calendar and have now created a division calendar for our office to add dates. We want a reminder to appear as it does in our personal calendar. The personal calendar will send remindes, but not the division calendar. Is there a way to have two calendars send reminders? Thanks. -- pat "pat" <pat@discussions.microsoft.com> wrote in message news:FAF6D3B2-6B45-4D4E-97D3-931D87F01A67@microsoft.com... > we are using Outlook 2007. We each have a personal calendar and have now > created a division calend...

Screen flashing using BeginPaint(), DrawText(), EndPaint()
I made a grid using CStatics to display text data and it works great. Now I'm trying to paint the text on the CDialog in it's OnPaint() member function. I am getting screen flashing/flickering when I use this method during repetitive screen updating (like when you hold down pgdown to scroll through records). Why? Here's the method I'm trying // in OnPain PAINTSTRUCT ps1 CDC *dc1 dc1 = BeginPaint(&ps1); for (i=0;i<rowmax;i++ for (j=0;j<colmax;j++ dc1->DrawText(m_str1,16,&m_r1,0) EndPaint(&ps1); Also, how to you set the font for a DC? I just can...

Excel Formulas using "IF"
If I enter the word "bounced in one cell can I trigger a value ($500.00) to become a negative number (-$500.00)? Regrads, Corey Brock Maybe you could use a helper cell with a formula: =if(a1="bounced",-b1,b1) Corey Brock wrote: > > If I enter the word "bounced in one cell can I trigger a > value ($500.00) to become a negative number (-$500.00)? > > Regrads, > Corey Brock -- Dave Peterson See your other post! -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, ...

We can not use linked table (to Excel) at the same time as two users!
I have a table which receives data from an Excel file which is located under server (Same location with access database file). When two users open this file, we can not use this table (linked to Excel file). I mean the one who open the access file first can use data on that table. But when second open and try to use that table, he fails. Is there any way to fix this problem. We are twu users only and we cant use it at the same time. Thanks In the Excel file that is the problem, try opening it and from the meny select Tools, Share Workbook. Check the box on the Edit tab that says Allow chan...

How can I use a named range for "pick from drop-down list"?
When the (right-click) "Pick From Drop-Down List..." command is used in Excel to facilitate key-entry, it only works for contiguous entries in cells in the column above the input cell. blank cells in between break the link. Is there a way to designate a named range of terms as the basis for the "drop-down" list? Microsoft,this should be a standard option! You can use data validation>make a list >name it >in the data>validation>select list >=mylist -- Don Guillett SalesAid Software donaldb@281.com "InstantZen" <InstantZen@discussions.m...

linked Excel charts don't match formatting in original
I have one spreadsheet in which I created charts linked to worksheets in the same file. These charts I have exported (i.e. copied into another file). When I open that other file without having the original file with the data open, the labels on the charts don't reflect the number formatting that was selected for them: .09 instead of 9%. Any help would be appreciated as I can't figure out how to fix this. For instance, is there a way to break the link to freeze a certain look of the charts? Thanks, Stefan. You could copy and paste the poicture rather than the link. If you...

I can not print a banner using microsoft pulisher 2000?
I am having problems printing a banner that I just made, it will only print the first part and not the other part of the banner. I need help Did you check your printer setup? -- JoAnn Paules MVP Microsoft [Publisher] "Vanessa" <Vanessa@discussions.microsoft.com> wrote in message news:0F2E8895-F2F6-4EA5-8ECB-B21486EEF0CE@microsoft.com... >I am having problems printing a banner that I just made, it will only print > the first part and not the other part of the banner. I need help > When you go to print, in the print dialogue, click "change overlap&quo...

Anyway to replace absolute file path in formula with relative or use Info(directory)?
Hi, I have a workbook that looks summarizes data that is in multiple (60) other workbooks, and want to know if there is someway to craft my look such that the folder structure doesn't manner --I want to be able to share this workbook with a teammate and not have the lookups fail, so long as the folder structure at the file level is the same. Here is the setup: Summary file C:\xxx\xxx\xxx\summary file.xls Data files C:\xxx\xxx\xxx\data files\folder1\data.xls C:\xxx\xxx\xxx\data files\folder2\data.xls .... C:\xxx\xxx\xxx\data files\folder60\data.xls I want to somehow craft my lookup -- r...

Cant use a signature
When I go to tools options and try to set a defoult signature, right when i click on the signature that I want to use I get an error and asks if I want to send the message to microsoft ( I choose not to send). Can anyone help me with this problem?? FYI. I'm using outlook2003 microsoft exchnage. "jp614" wrote: > When I go to tools options and try to set a defoult signature, right when i > click on the signature that I want to use I get an error and asks if I want > to send the message to microsoft ( I choose not to send). Can anyone help me > with this problem...