Reputation: 33
I am trying to write a function to search for a specific date, entered as a parameter, in a range of cells in an adjacent worksheet. On finding the date, the function should return a string, "found: " and the cell reference.
All seems to be working well enough, but the function returns 'nothing' even when there is a (deliberately entered) date, in date format, both in the cell range and the cell referred to when the function is called.
Have I missed something critical when calling find when using a Date?
A note, the function looks in the same row that it is called from, in the other sheet. This may help explain how i'm setting rng
Public Function d_scan(targ As Date) As String
Dim ws As Worksheet
Dim targetSheet As Worksheet
Dim ret As String
Dim rng As String
Dim scanner As Date
Dim found As Range
Set targetSheet = ThisWorkbook.Worksheets("2018")
Set ws = Application.Caller.Worksheet
Let intRow = Application.Caller.Row
Let intCol = Application.Caller.Column
Let rng = "F" & intRow & ":" & "X" & intRow
Set found = targetSheet.Range(rng).Find(What:=targ, LookAt:=xlWhole)
If found Is Nothing Then
Let ret = "nothing"
Else
Let ret = "found: " & found
End If
d_scan = ret
End Function
Upvotes: 1
Views: 8030
Reputation: 13386
date issues are quite subtle and their solution may depend on the actual scenario (what variable type is used, what data format is used in the sheet,...)
for a start, you may want:
specify all relevant Find()
method parameters, since undefined ones will be implicitly assumed as per its last usage (even from Excel UI!)
convert Date
to String
via the CStr()
function
so, you may want to try this code:
Option Explicit
Public Function d_scan(targ As Date) As String
Dim rng As String
Dim found As Range
Dim intRow As Long
intRow = Application.Caller.Row
rng = "F" & intRow & ":" & "X" & intRow
Set found = ThisWorkbook.Worksheets("2018").Range(rng).Find(What:=CStr(targ), LookAt:=xlWhole, LookIn:=xlValues) ' specify 'LookIn' parameter, too
If found Is Nothing Then
d_scan = "nothing"
Else
d_scan = "found: " & found
End If
End Function
Upvotes: 3
Reputation: 603
I think you are comparing day/hour/minute/second with day/hour/minute/second and getting no matches (everything's too specific). I used this to massage targ into "today" at 12:00 AM, but you would need to do something to massage the data on the sheet like this as well for the range.find to work.
targ = Application.WorksheetFunction.Floor(targ, 1)
I suggest using a method other than range.find... Looping perhaps, looking for a difference between targ and the cell that's less than 1?
Upvotes: 1