date function
 

I am trying to make a function that will search a range of dates (the
first argument) and if it matches the date in the second argument will
return true. I keep getting #VALUE errors with the second argument.
I'm a bit stuck. I'd appreciate any help I can get.


Function MatchDate(TPRrange As range, TPRDate As range) As Boolean
Application.Volatile (True)
Dim c
For Each c In ActiveWorkbook.range(TPRrange)
If c.Value = range(TPRDate) Then MatchDate = True
Exit For
Next
MatchDate = False
End Function


Function MatchDate2(TPRrange As range, TPRDate As Date) As Boolean
Application.Volatile (True)
Dim Index As Variant

' Find a match for the serial value of the date in the range A1:A10
' on Sheet1.
Index = Application.Match(CLng(TPRDate), range(TPRrange), 0)

' Display the results.
If IsError(Index) Then
MatchDate2 = True
Else
MatchDate2 = False
End If
End Function


Thanks,
G

date function
 

Since you're passing a range to that function, shouldn't this:
Index = Application.Match(CLng(TPRDate), range(TPRrange), 0)
be
Index = Application.Match(CLng(TPRDate), TPRrange, 0)

(I don't see a reason to make the function volatile, either.)



wrote:

I am trying to make a function that will search a range of dates (the
first argument) and if it matches the date in the second argument will
return true. I keep getting #VALUE errors with the second argument.
I'm a bit stuck. I'd appreciate any help I can get.

Function MatchDate(TPRrange As range, TPRDate As range) As Boolean
Application.Volatile (True)
Dim c
For Each c In ActiveWorkbook.range(TPRrange)
If c.Value = range(TPRDate) Then MatchDate = True
Exit For
Next
MatchDate = False
End Function

Function MatchDate2(TPRrange As range, TPRDate As Date) As Boolean
Application.Volatile (True)
Dim Index As Variant

' Find a match for the serial value of the date in the range A1:A10
' on Sheet1.
Index = Application.Match(CLng(TPRDate), range(TPRrange), 0)

' Display the results.
If IsError(Index) Then
MatchDate2 = True
Else
MatchDate2 = False
End If
End Function

Thanks,
G

--

Dave Peterson

date function
 

You're right, it doesn't need to be volatile. In the first function I
tried it as a range but got #VALUE! errors. In the second function I
tried it as a date but still got #VALUE! errors with the second
argument.

date function
 

If you're passing ranges, then you don't need to use syntax like:

Function MatchDate1(TPRrange As range, TPRDate As range) As Boolean
Application.Volatile (True)
Dim c
For Each c In ActiveWorkbook.range(TPRrange)
If c.Value = range(TPRDate) Then MatchDate = True
Exit For
Next
MatchDate = False
End Function

Becomes:

Function MatchDate1(TPRrange As range, TPRDate As range) As Boolean

Dim c as range
For Each c In TPRrange
If c.Value = TPRDate.value Then MatchDate = True
Exit For
Next
MatchDate = False
End Function

And....

Function MatchDate2(TPRrange As range, TPRDate As Date) As Boolean
Application.Volatile (True)
Dim Index As Variant

' Find a match for the serial value of the date in the range A1:A10
' on Sheet1.
Index = Application.Match(CLng(TPRDate), range(TPRrange), 0)

' Display the results.
If IsError(Index) Then
MatchDate2 = True
Else
MatchDate2 = False
End If
End Function

becomes

Function MatchDate2(TPRrange As range, TPRDate As Date) As Boolean

Dim Index As Variant

' Find a match for the serial value of the date in the range A1:A10
' on Sheet1.
Index = Application.Match(CLng(TPRDate), TPRrange, 0)

' Display the results.
If IsError(Index) Then
MatchDate2 = True
Else
MatchDate2 = False
End If
End Function



wrote:

You're right, it doesn't need to be volatile. In the first function I
tried it as a range but got #VALUE! errors. In the second function I
tried it as a date but still got #VALUE! errors with the second
argument.

--

Dave Peterson