Worksheet.Match the best way to go?

[Ed]

I'm trying to determine if a given date exists within a list of dates. The
best my limited skills could produce was to use the Match function and check
for an error if the number wasn't in the list. Is there a better way? What
I have looks like this:

Sub Foo_TestMatch()

Dim wks As Worksheet
Dim rng As Range
Dim x As Long, y As Double

Set wks = ActiveSheet
Set rng = wks.Range("A1:A10")
x = 12
Err.Clear
On Error Resume Next
y = Application.WorksheetFunction.Match(x, rng, 0)
If Err.Number <> 0 Then MsgBox Err.Number
Err.Clear
On Error GoTo 0

End Sub

(To test this, I was just using numbers, so that's why nothing is a date.)

Ed

 

[Guest]

Here is what I would probably do, I hard coded the testTxt, but you could use
a reference to a cell or use "testTxt=InputBox(Enter search value":

Public Const TARGET_COL = "A"
Public Const START_ROW = 1

Public Function Foo_TestMatch() as Boolean

Dim testTxt As String
Dim lastRow As Long
Dim rowIndex As Long
Dim matchFound As Boolean

'Enter test value here
testTxt = "2/20/07"

'Grab last row in worksheet
lastRow = Range(TARGET_COL & "65536").End(xlUp).Row

'Loop to find value
matchFound = False
rowIndex = START_ROW
While Not matchFound And (rowIndex <= lastRow)
If CDate(Range(TARGET_COL & rowIndex)) = CDate(testTxt) Then
matchFound = True
Else: rowIndex = rowIndex + 1
End If
Wend

'Set function result
Foo_TestMatch=matchfound

'You may also want to alert user
If matchFound Then
MsgBox "The text " & testTxt & " was found in row " & rowIndex & ".",
vbExclamation
Else: MsgBox "The text " & testTxt & " was not found.", vbExclamation
End If
End Function