How to detect if there is no match?

nicholasho

New Member
Joined
Oct 10, 2005
Messages
6
Hi,

I am writing a macro to retreive values from a sheet based on values of another sheet. The following statement is trying to do the work:

Set RangeOne = Worksheets("Sheet5").Range("AX2:AX2503")
.
.
Agreement = Application.WorksheetFunction.Index( RangeOne, Application.WorksheetFunction.Match(Order, Worksheets("Sheet5".Range("A2:A2503"), 0))

Current problem is that run time error will issue if there is no match of Order value. I like to assign blank value to Agrement if Order value is not found.

How can I detect the error situation?

Thanks and Regards
Nick
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

AgentSmith

Well-known Member
Joined
Mar 8, 2004
Messages
575
Use "CountIf" to calculate the number of matches. If that's zero then return your blank value.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,334
Messages
5,571,589
Members
412,407
Latest member
ElmerCC
Top