worksheet function match syntax issue

[vavs]

I am writing a vba script that will look up the row of a time function. I have a named range Date_Time that refers to a range in worksheet "Schedule" of A:A. Here is what I have :

Dim vlline as long

vlline = Application.Worksheetfunction.Match(stDate,Range(Date_Time))

Note stDate is defined in a cell with data validation of the range Date_Time.

I get an error 1004

I have tried to change the match function also to (stDate,Worksheets("Schedule").Range("A:A")) and that does not work either.

What am I missing?

[NoSparks]

On my system Application.Worksheetfunction.Match never works. I have to omit the Worksheetfunction word and use just Application.Match which works even tho when doing this Match doesn't appear in the list of functions that pops up after typing in Application. Have no idea why, but works for me.

[GarysStudent]

Give MATCH() a Range as the first argument:


Sub durl()
Dim d As Range
Dim Date_Time As Range
Dim N As Long
Set Date_Time = Range("B2:B16")
Set d = Range("B6")
N = Application.WorksheetFunction.Match(d, Date_Time, 0)
MsgBox N
End Sub

[SamT]

If match_type is 1, MATCH finds the largest value that is less than or equal to lookup_value. Lookup_array must be placed in ascending order: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.

If match_type is 0, MATCH finds the first value that is exactly equal to lookup_value. Lookup_array can be in any order.

If match_type is -1, MATCH finds the smallest value that is greater than or equal to lookup_value. Lookup_array must be placed in descending order: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.

If match_type is omitted, it is assumed to be 1.




On Error Resume Next
vlline = Range(Date_Time).Find(stDate).Row
If Range(Date_Time).Find(stDate) Is Nothing Then MsgBox "Date Not Found"