Match Function Problem!!! (RESOLVED)

[Avatarp]

VBA Excel

I keep getting an Error 2042 returned by the match function here. I have carfully set my Range variables to the correct columns and rows. The range objects are all dates. It seems no matter what I do I get that error. Any ideas? Here is the code:

Sub test()
    Dim matchstart As Range
    Dim matchend As Range
    Dim matchRange As Range

    c = CDate("1/28/2004")

    Set matchstart = Workbooks("datafile.xls").Sheets("futexpiry").Cells(2, 5)
    Set matchend = Workbooks("datafile.xls").Sheets("futexpiry").Cells(500, 5)
    Set matchRange = Workbooks("datafile.xls").Sheets("futexpiry").Range(matchstart, matchend)
    FirstMatch = Application.Match(c, matchRange, 1)
End Sub

[salvelinus]

Where do you Dim c?

[Avatarp]

I actually don't have to Dim anything.

[salvelinus]

Well, that's not a good idea...
Anyway, MSDN has two types of listings for error 2042. One is ResourceNameSuffixTooLong, the other is a SQL error. Are you sure the error is coming in that code? Try stepping through it.
Or look here.

[Avatarp]

I have been staring at this problem for a while now. stepping through it FirstMatch = error 2042

[Avatarp]

I know that 1/28/2004 is in the range I assigned. So shouldn't this be a no brainer lol...

[salvelinus]

Try msgboxing your values before you hit FirstMatch, make sure they're what you think they area. I don't work much with Excel.

[Avatarp]

I added a watch on the values so I can see what they are.

[salvelinus]

Try this Google thread. Seems to discuss your problem. At a glance, looks like it's not finding c.

[Avatarp]

I've looked at that string and many more but it just came to me. When working with dates I should convert any of those date variables to Doubles. Even though the Spreadsheet Range shows dates in Date Format they are actually Numbers. Thanks for helping me out here. I think I got it now.