Excel VBA Range.Find Date That is a Formula

Question

I receive a workbook twice a month 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.

Within this workbook, one sheet contains information about processing errors. It looks like this:

(team members' user IDs redacted)

As you can see, each date is represented by a merged 1x3 range with the date in question formatted as "dd-mmm" e.g. "01-Jun"

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

I am attempting 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 - but to no avail. Here's what that looks like:

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

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

I have scoured the usual resources and tried everything I have seen suggested. Really hoping someone here knows something.

UPDATE:
I have written the following code 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

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.

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)

Answer 3

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