CODEWITHSUNDEEP
vbaexcel
bjm
bjm

Reputation: 33

VBA - search for a date in a range of cells, returning cell address

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

Answers (2)

DisplayName
DisplayName

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

elliot svensson
elliot svensson

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

Related Questions