match vs .find? What's better/preferred?

[AB]

Hi All,

I'm writing a code to do list reconciliation, i.e., the code would
cycle through one list of account numbers and look for a match in 2nd
list and do some stuff if match found. The match always needs to be
exact.

I usually in my codes use the .find xlwhole method - something along
the lines:
dim r as long
dim c as object
set c=myrange.find MyVal, xlwhole ...

if not c=nothing then r=c.row
....
to get the row of the same account in the 2nd list.
Currently users are using a worksheetfunction Match to do the same
thing but just manually.

So, I was about to substitute that manual practice with a code and use
the .find instead of the appliation.worksheetfunction.match (or
application.match) but before I do that I thought i'd rather check
with experts here whether the .find xlwhole is in no way inferior to
the Match(Range,Val,0) function currently utilized.
The searched/matched values might be both Integers and also Strings
(rather long ones - maybe 20-30 chars) - does the string lenght make
any difference?

Any considerations appreciated!

 

[Paul Wilson]

Personally I would always use Excel's worksheet lookup functions when doing
this sort of task, even programmatically.
Agreed, the code is a bit more obtuse, but Match and Index are amoung the
most useful functions.

Paul

 

[Dave Peterson]

I like the .find better.

And I'd make sure that all the parms are specified in the .find command.

 

[Peter T]

One more view -
There are some differences in the way Find and Match compare, eg as regards
cell.value vs cell.text, numberformats etc.

If you need to cater for older versions note Find has fewer arguments, also
the Find function fails in a UDF.

Which will suit your needs best, Find or Match, is likely to depend on your
overall scenario. Test both!

Regards,
Peter T

 

[Dave Peterson]

Find will work ok in UDFs called from worksheet functions if you're using xl2002
(IIRC) or higher.

 

[AB]

Thanks for your replies!
From which I gather that there are no technical superiority in either
and i just should make up my mind and test both.

A further question, however, if I may - with regards to this Peter
note:

'There are some differences in the way Find and Match compare, eg as regards
cell.value vs cell.text, numberformats etc. '

If I define my variable as either .text or .value and then do
the .find or worksheetfunction.match using that variable - would the
two behave differently anyway? How exactly they do compare differently
(i.e., what should I watch out for)?
(I'm on xl2002 and xl2003)

 

[Peter T]

Technical superiority of find vs match is probably not the way to look at
it. If the match-type (or range-type with a lookup) predicates a sorted list
nothing's going to beat that (notwithstanding the overhead of calling the
function). However if you want to search a whole sheet then for sure use
Find. Also if you want to find multiple matches Find is probably easier to
work with

Differences in how they 'compare', I was hoping you weren't going to ask!
But there are plenty of differences, mainly subtle ones. Try these contrived
examples with both Find & Match:

0 (zero) unformatted and formatted as a date
Boolean True/False
Find is optionally case sensitive, Match is non case sensitive
With some numberformats Find will find what's displayed (ie the text)
whereas Match finds the underlying value (but it depends on the format).
I'm sure there are loads more differences, but I forget!

Regards,
Peter T

 

[AB]

Thanks Peter!
I believe then that with my .text as search value I should be fine
with either. Will run some more tests and will call it a day.
Thanks again.