unable to get match property of WorksheetFunction class

[titus]

I have the following code :

If IsError(Application.WorksheetFunction.Match(cells(x, 1),
Worksheets("Sheet1").Range("A2:A15"), 0)) Then

do this

else

do that

The code works fine when there is a match ( for every cell in column A
of worksheet 2 it finds a match with the range A2:A15 in worksheet 1),
but I get the error 'unable to get match property of WorksheetFunction
class' when there is no match found i.e it gives a #N/A error. I dont
know what this error means. Any ideas ? Thanks

titus

 

[titus]

I have the following code :

If IsError(Application.WorksheetFunction.Match(cells(x, 1),
Worksheets("Sheet1").Range("A2:A15"), 0)) Then

do this

else

do that

The code works fine when there is a match ( for every cell in column A
of worksheet 2 it finds a match with the range A2:A15 in worksheet 1),
but I get the error 'unable to get match property of WorksheetFunction
class' when there is no match found i.e it gives a #N/A error. I dont
know what this error means. Any ideas ? Thanks

titus

My bad. I searched this group and found the answer. The correct code
is:

If IsError(Application.Match(cells(x, 1),
Worksheets("Sheet1").Range("A2:A15"), 0))

Though I still dont get why application.worksheetfunction.match gave
me the error when it returned #N/A

 

[Dave Peterson]

drop the .worksheetfunction stuff from your expression.

Application.match() will return an error if there is no match.

Application.worksheetfunction.match() will raise a trappable error that you have
to program against.

Application.match() is much simpler to use in my opinion, but you could do
something like:

dim res as long
res = 0
on error resume next 'avoid that blow up!
res = application.worksheetfunction.match(....)
if err.number <> 0 then
'no match found
err.clear
'do what you want
else
'a match was found
end if

 

[titus]

drop the .worksheetfunction stuff from your expression.

Application.match() will return an error if there is no match.

Application.worksheetfunction.match() will raise a trappable error that you have
to program against.

Application.match() is much simpler to use in my opinion, but you could do
something like:

dim res as long
res = 0
on error resume next 'avoid that blow up!
res = application.worksheetfunction.match(....)
if err.number <> 0 then
'no match found
err.clear
'do what you want
else
'a match was found
end if

Thanks Dave for the tip