MATCH function in VBA for dates

[mdarby]

I am trying to use the MATCH function in VBA to search for a particular date in a range containing dates and return the matching row number. I can accomplish this in the Excel worksheet but get errors when implementing it in Excel ("type mismatch" I think.)

Several posts have alluded to Application.WorksheetFunction.Match not working properly in VBA when used with dates. Is this in fact true, or is there a workaround? I get the impression that the problem might be whether the dates are seen as unformated (eg. 38675) or formated (1/5/2002). Is there a way to make sure VBA sees both the lookup data and the date range in the same way?

I currently have to perform the calculation in some scratch cells and read back the result into VBA. But it would be cleaner to understand if it can be performed in VBA itself.

Thanks, and aplogies if this has been resolved earlier.

 

[le Wizzard]

You want to search for the value contained in the range. So start by replacing "A5" with Range("A5").Value

Tracker_Col = Application.Match(Range("A5").Value, Worksheets("SC").Range("A4:GH4"), 0)


Also: your search value has spaces - not sure if that will be an issue.
Good luck!

 

[Tatumsa]

Hi,
Apologies for necroing this thread, but I have had a similar problem where I need to match on a date.
I couldn't get CDbl to work so thought of using DateSerial which worked fine.
I am using Excel 2007 and in the UK so my date format is "dd/mm/yyyy" so you'd need to change the Mid positions.
Columns(1) is a list of sorted, non-consecutive dates and there are 100K+ rows in my worksheet hence all the Longs.

    lngStrDateSerial = DateSerial(Mid(strDate, 7, 4), Mid(strDate, 4, 2), Mid(strDate, 1, 2))
    If IsError(lngStartRow = Application.Match(lngStrDateSerial, wsDataSheet.Columns(1), 0)) Then
        GetDateServers = 0
        Exit Function
    Else
        lngStartRow = Application.Match(lngStrDateSerial, wsDataSheet.Columns(1), 0)
    End If
    
    Other stuff gets done down here.