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

Monsour

Range.Find persists its optional parameters between calls - a nasty bug-prone behavior; make sure you specify all parameters every time to avoid it biting you. – Funereal

if you want to find an expression of a formula, wouldn't xlValues the right way? – Phrygian

@Mat'sMug thanks for that information. In this case, that's not an issue, but good to know for the future. @DirkReichel sadly, no. xlValues only examines the .Text property of what's displayed on the worksheet. – Monsour

and searching for Format(DateValue(minDate),"[$-en-US]dd-mmm") with xlValues also does not work? – Phrygian

Bizarrely, no, that does not work. Which actually suggests that xlValues is looking at more than just the .Text property... – Monsour

My suggestion to your code would be UsedArr = SrcWS.UsedRange.Value2 and If UsedArr(i, j) = CDbl(MinDate) Value2 is a bit faster, and completely removes any formatting issues from consideration. Value will work, but requires more conversions internally to equate. – Gaseous

Thanks - I made the switch. The code runs quickly and produces the expected outcome. – Monsour

Answer 1

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.

Gaseous

Yes, the second method is pretty much bullet-proof, especially in your situation. – Gaseous

I don't understand why, but that first method does not work. The second method, however, does. I have added the code I wrote to do this to my original post. Thanks! – Monsour

In short, never use Range.Find to find dates. – Riccardo

Answer 2

@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)

Chinaware

No, this does not work, because in this case, the xlFormulas value is the formula and not the date. – Monsour

This help you to find the Date. Private Sub Finddate () Dim dateval , Dim Date row, Dim DateCol dateval = Range("A8").Value Cells.Find(What:=dateval, After:=ActiveCell, LookIn:=xlValues, LookAt:= _ xlPart,SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _ , SearchFormat:=False).Activate DateRow = ActiveCell.Row DateCol = ActiveCell.Column MsgBox (DateRow & " " & DateCol) End Sub – Fenwick

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.

Philpot

Answer 4

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 

Secondclass

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

January

I get a generic error 1004 when I try to set the FindFormat exactly as you outlined above. – Monsour

Thank you for pointing this out. Perhaps it's because I had cell A1 activated when I ran the code and you did not. In any case, I've now modified the code to make sure that doesn't happen, and provided an animated gif to show the code running. – January

This animation is really annoying. Aren't you able to post this without the animation? – Disport

Answer 6

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.

Tyishatyke