Range.Find on a Date That is a Formula

user_8451775 0
Viewed 39158

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.
screenshot
(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
vba excel date
4 Answers

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.

user_43220820
Related