Application.Match Function fails with dates

[mosta]

I see both of these threads:

http://www.mrexcel.com/forum/showthread.php?t=68318

http://www.mrexcel.com/forum/showthread.php?t=450418

but I can't get Application.Match to work with a date and a date array. CDate and CLng on the date, don't help (and can't be applied to the array). And my debugging code shows that the date variable is equal to the second value in the array:

Dim x() As Date
x = p_dtExpiryArray

Dim y As Boolean
y = (dtExpiry = p_dtExpiryArray(2))

iCol = Application.Match(dtExpiry, p_dtExpiryArray, 0)

In the locals window, x is type "Date(1 to 2)". And the value of y is "TRUE". So, yes, they match--they do! But all I get is Error 13, Type mismatch, on the iCol =, last line. (And I can't get WorksheetFunction.Match or Application.WorksheetFunction.Match to work at all (1004--can't get the property).

I guess I just have to use a Do loop, through the array. But it really should be a 1-line solution, and I successfully match doubles and strings in the same code...

 

[AlphaFrog]

If dtExpiry is a two element date array Date(1 to 2), and you use it in the .Match function without specifying which element you want to match, then iCol has to be of type Variant so as to receive an array result. The type mismatch may be with iCol.

Dim iCol as Variant
iCol = Application.Match(dtExpiry, p_dtExpiryArray, 0)

Or match only one element from dtExpiry

Dim iCol as Long
iCol = Application.Match(dtExpiry[COLOR="Red"](1)[/COLOR], p_dtExpiryArray, 0)

 

[mosta]

Thanks for the suggestion. I think the issue is not with dtExpiry, however, as that is a simple date type, not an array.

Here are the two analogous lines of code, where Matching a date in a date array does not work, but Matching a double in a double array does work:

Private p_dbStrikeArray() As Double
Private p_dtExpiryArray() As Date
'Class level variables above. Method/Sub code below. 

Dim iRow As Long, iCol As Long
Dim dbStrike As Double
Dim dtExpiry As Date

iRow = Application.Match(dbStrike, p_dbStrikeArray, 0)    'Good

iCol = Application.Match(dtExpiry, p_dtExpiryArray, 0)    'No good

And I also used this debugging code:

 Dim x() As Date
x = p_dtExpiryArray

Dim y As Boolean
y = (dtExpiry = p_dtExpiryArray(2))   'y is TRUE

 

[AlphaFrog]

Can you show how you are assigning a date to dtExpiry and populating p_dtExpiryArray ?

 

[mosta]

    For Each vaKey In p_diPositions.Keys

        stExpiry = Split(vaKey, "|")(0)

        dtExpiry = CDate(stExpiry)
        
        dbStrike = Split(vaKey, "|")(1)

There is a Positions Dictionary. The keys are made up of some pertinent info, demarcated in parts by "|", converted into and out of string form as needed. The above code is representative of what is done both at the point that I am trying to use the .Match function, and in earlier steps where the two arrays are constructed. Type checking at all steps has so far confirmed that I am getting dates and doubles, both in the single variables and in the arrays, as expected. (Eg, I confirmed that dbStrike is going from string to double, from the Split function, without the use of CDbl( ).) And element "(0)" of Split(vaKey...) comes from:

stExpiry = Format(dtExpiry, "DD-MMM-YYYY")
...
stKey = stExpiry + "|" + CStr(NewDeal.Strike)

In the threads I reference above and searching elsewhere I've seen comments to the effect that .Match just isn't really reliable with dates. But then they always go on to suggest a fix (eg, CDate or CLng)--and then it works for everyone else but me.

 

[shg]

You haven't posted the code that doesn't work ...

 

[mosta]

I did:

iCol = Application.Match(dtExpiry, p_dtExpiryArray, 0)       'No good

All I get is Error 13, Type mismatch, on the iCol =, last line.