Range.Find on a Date That is a Formula

Question

I receive a workbook that contains information about the processing volumes of a call center team. I have no way of modifying the format or layout of the workbook upstream.

One sheet contains information about processing errors.

(team members' user IDs redacted)

Each date is represented by a merged 1x3 range with the date in question formatted as "dd-mmm" e.g. "01-Jun"

That date is pulled via formula from another sheet with the same layout. The formula for one such range reads: ='QA Scores'!K2:M2

I attempted to use Range.Find to locate the first day of a given month and an end date in that same month (based on user input) - e.g. June 1 through June 15.

Set rngMin = .Find(What:=DateValue(minDate), _
                   LookIn:=xlFormulas, _
                   LookAt:=xlWhole)

In other uses, I located a date in this manner, but the added complexity of the value coming from a formula seems to be the issue here.

UPDATE:
I have written the following based on Ron Rosenfeld's answer:

Dim UsedArr As Variant: UsedArr = SrcWS.UsedRange
blFound = False
For i = LBound(UsedArr, 1) To UBound(UsedArr, 1)
    For j = LBound(UsedArr, 2) To UBound(UsedArr, 2)
        If UsedArr(i, j) = MinDate Then
            blFound = True
            Exit For
        End If
    Next
    If blFound = True Then Exit For
Next

Answer 1

After scratching my head around this problem, I found out simple solution. The trick is just to use Application.Match(). Do not use WorksheetFunction.Match(), because Application.Match() has one BIG advantage when value is not found:

• Application.Match() simply returns Error object, and you can test for it with IsError() function

•WorksheetFunction.Match() instead raises exception, and you have to deal with it with that pesky On Error... construction.

One notable difference is that variable, being assigned to, MUST be of type Variant, since only this type can hold Error type.

EXAMPLE:

Dim vRow As Variant
Dim seek_date As Date
Dim rngDate As Range
seek_date = DateSerial(2023, 12, 26)
'// Search for date in column "A"
vRow = Application.Match(seek_date, Columns(1), 0)
'// vRow can hold Error object. That is why it is Variant.
'// Check if it does not hold Error (i.e. date was found).
If Not IsError(vDate) Then
  '// OK. Date was found. Deal with it.
  '// vRow holds numeric value (row number).
  '// For example, get the range with date:
  Set rngDate = Cells(vRow, 1)
Else
  '// Date was not found
End If 

Answer 2

To search for dates, first you have to save it in a String variable and give it the format "Short Date". Then Find the variable by converting it to date.

This function is quite generic but easy to adapt to any question. Write the function and its call in a standard module. In ActiveSheet write the date 25/12/2023 in the format you want (for example 25-dic-23) and launch the DateFind_Call call

Option Explicit

Public Function DateFind(ByVal shSheet_I As Worksheet, ByVal dDate_I As Date) As String
    DateFind = ""
    Dim StrDate As String
    StrDate = Format(dDate_I, "Short Date")
    
    Dim DateCell As Range
    Set DateCell = shSheet_I.Cells.Find( _
        What:=CDate(StrDate), _
        After:=Range("A1"), _
        LookIn:=xlFormulas, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        LookAt:=xlWhole)
    If Not DateCell Is Nothing Then
        DateFind = DateCell.Address(False, False)
    End If
End Function

Sub DateFind_Call()
    Dim MyDate As String
    MyDate = DateFind(ActiveSheet, CDate("25/12/2023"))
    If MyDate <> "" Then
        MsgBox "Date found in range: """ & MyDate & """"
    Else
        MsgBox "Date not found"
    End If
End Sub

Visit https://www.ozgrid.com/VBA/find-dates.htm for more details.

Answer 3

Been facing the same issue when using .find for dates. Even if the Target Date Format for search is in dd-mmm Use .find with d-mmm format.

Date_Value = Application.Text(Date_Value, "d-mmm-yy") 'Use Format for your case

Set r_gt= .Range(EODate_Range).Find(Date_Value, LookIn:=xlValues)

PS: If the target date format is dd-mm-yy, you'll have to loop through the cells. Tried the above code for 01-01-20 after changing search format to d-mm-yy / d-m-yy and it failed to find the date, but it finds the date if cell format is changed to 01-Jan-20.

Answer 4

@KOstvall,, I would like to suggest this simple find method, since you are trying to get the Date.

Set rng = Sheet1.Range("A:A").Find("1/1/2017", LookIn:=xlValue, LookAt:=xlWhole)

Answer 5

Update: I've modified the code below to set the active cell before the find. The second animated gif show the code running

One approach is to do a find based upon the specific date format, which in your case appears to be "[$-en-US]dd-mmm;@". The following is a simple example, which you could then adjust to your need. To make this example work, place 3 dates into "A1:A3", with just one of them having the desired format, and then run the macro. The animated gif shows how to set the format.

Sub dateFormatFind()
Dim sh As Worksheet, searchR As Range
Dim cell As Range, resultR As Range
Set sh = Worksheets("Sheet5")
Set searchR = sh.Range("A1:A3")
Set resultR = sh.Range("C1")
sh.Range("A1").Activate
Application.FindFormat.NumberFormat = "[$-en-US]dd-mmm;@"
resultR = searchR.Find(What:="", After:=ActiveCell, _
LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=True)
End Sub

Answer 6

Dates are tricky to find with the Range.Find method. One of the issues is that in VBA, dates are of the Date data type, but the worksheet does not have that data type. Rather the data type is a number that is formatted to look like a date.

One solution, if you can be certain of the format of the date on the worksheet, is to search for the string equivalent. Given your example, something like this will work:

Option Explicit
Sub GetDates()
    Const findDate As Date = #5/11/2017#
    Dim findStr As String

Dim R As Range, WS As Worksheet
Set WS = Worksheets("Sheet1")

findStr = Format(findDate, "dd-mmm")

With WS
    Set R = .Cells.Find(what:=findStr, LookIn:=xlValues, lookat:=xlWhole)
    If Not R Is Nothing Then MsgBox findDate & " found in " & R.Address
End With

End Sub

but it is not very robust since, in many cases, the user can change the format.

Another method that is more robust, would be to loop through the existing cells, looking for the numeric representation of the date (using the Value2 property):

Sub GetDates2()
    Const findDate As Date = #5/11/2017#
    Dim R As Range, C As Range, WS As Worksheet

Set WS = Worksheets("sheet1")
Set R = WS.UsedRange

For Each C In R
    If C.Value2 = CDbl(findDate) Then MsgBox findDate & " found in " & C.Address
Next C
End Sub

If you have a large range to search, this can be sped up by a factor of ten by reading the range into a VBA array and looping through the array.