Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date function in Excel that updates only when a doc is changed? | Excel Worksheet Functions | |||
Date Function | Excel Discussion (Misc queries) | |||
date format and the RIGHT function | Excel Worksheet Functions | |||
Date function | Excel Worksheet Functions | |||
Is there a function to show future date | Excel Worksheet Functions |