match in vba

[Guest]

greetings, am using Application.WorksheetFunction.Match, using 0 - for exact
match, comparing two ranges (one in sheet1, other sheet2). i want to
highlight the columns of range in sheet2 which are not included in range in
sheet1. running into problem, i think, because when there is column which is
not in sheet1 range the function returns value equivalent to "N/A". have
tried using:

Application.WorksheetFucntion.IsNA(WorksheetFunction.Match()) but can't get
this to work. any and all suggestions appreciated, thanks!

 

[Dave Peterson]

First, you'd have to spell .worksheetfunction correct to even get close!

But I'd drop the .worksheetfunction completely.

dim res as variant
.....

res = application.match(someval, somerng, 0)
if iserror(res) then
'not found
else
'is found
end if

There's a difference between the way VBA uses application.match() and
application.worksheetfunction.match().

The first returns an error you can check with iserror().

The second causes a runtime error that you have to code around.

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

I find using application.match() easier.

 

[Dave Peterson]

Ps. The same distinction happens with application.vlookup() and
application.worksheetfunction.vlookup().

 

[Guest]

hi dave, that did the trick, thanks much. this might be really simple
question but, why the subtle difference between Application.Match and
Application.WorksheetFunction.Match? not complaining, after all difference
allowed for fix to problem, just curious.

oh, regarding spelling, i consider two outta three pretty good =D

 

[Dave Peterson]

I have no idea why there's a difference--just that there is.

And that's pretty good, too <vbg>.