Application.worksheetfunction.match

[Martin Beir]

Dear All,

I find that the following code:

Application.worksheetfunction.match(lookup_value,look up_array, 1)

works fine unless the lookup_value is a date when it fails though the match
function will work with dates when entered into a worksheet cell.

I can work through my array with For_Next or Do_Until but feel this should
not be necessary.

Any comments?

Martin

 

[JE McGimpsey]

I find that the following code:

Application.worksheetfunction.match(lookup_value,look up_array, 1)

works fine unless the lookup_value is a date when it fails though the match
function will work with dates when entered into a worksheet cell.

While it probably would be more helpful to have seen your code, one of
the problems with Match and dates is that Match looks at the underlying
cell value, so it attempts to match the serial number of the date, not
the displayed representation. Therefore one should look for the numeric
value.

In addition, VBA uses the 1900 date system rather than the Mac default
1904 date system, so one may need to correct for the difference.

This works for me, in the 1904 date system if 4/27/2007 is entered in a
cell in column A:

a = Application.WorksheetFunction.Match( _
CLng(#4/30/2007# - 1462), Range("A:A"), False)