How to detect if there is no match?

[nicholasho]

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

 

[AgentSmith]

Use "CountIf" to calculate the number of matches. If that's zero then return your blank value.