Finding a Date with the Find Command

Here is the offending line:

Set rngD = shS.Range(shS.Cells(HomeRow, 1), shS.Cells(HomeRow, 52)).Find(clD.Offset(3, 0).Value, _
                LookIn:=xlValues, lookat:=xlWhole)

The value I am looking for is 6/14/2017, the value in the cell is 6/14/2017 - yes both are dates and not strings. Home row is the row that contains the dates. It also contains other garbage like strings for header "W/E x/y" - could this garbage interfere with the search. All the web searches I've done says I'm doing it right. I have US settings on my computer and both this spreadsheet and the workbooks that it reads are on the same settings.

A version of this program that looks for the strings matching the W/E dates works perfectly. I'm trying to adapt it to find a date rather than a string.

The zip file contains the program and two sample files: on the program file, In Cell B1 on the control panel enter the path name of the directory where the data files are. Put the data files in this directory. The output should provide two lines of data on the backlog sheet: one for Sample Data Sheet A and another for Sample Data Sheet B.

Have you tried using Application.Match and Value2?

That worked for me and returned the column, 20, the date was in.

Res = Application.Match(cld.Offset(3,0).Value2, shs.Cells(homeRow).resize(,52),0)

MsgBox cld.Offset(3,0).Value2 & " is in column " & Res

I have not but I will try it. I was hoping to use a "native" VBA call than a worksheet function, but it's a quick run, so it probably doesn't matter. Too close to quitting time to try it now . I'll call back in the morning.

You did drop something new on me: what does .resize do?

Well that should be Resize, I was working in the Immediate Window so it didn't get changed to proper case.

Anyways, it well, resizes a range - in this case it resizes the range to search in by 52 columns.

By the way, there's a typo in the code I posted - it should be shs.Cells(homeRow, 1) not shs.Cells(homeRow).

I did kind of get away with that though because Cells(1) is equivalent to Cells(1,1), ie it's the very first cell in the sheet, A1.

I've made the adjustment and it does seem to work. Thanks for the correction on the typo. HomeRow doesn't always work out to 1. The people who track this data play loose with the format of the report. Our Phoenix office uses row 1 to record the daily temperature. Some offices start in column A others in column D.

I have another question: how do I test for the condition when the formula does not find a match - I want the program to "die gracefully."

Finally. I have used the Find command to find dates in other programs successfully using the exact syntax of the regular post. I will have to go back and figure out what might be different about those programs. One of the things I can think of is that there are strings that are also interspersed in the range in which I am looking. I'll set up a test for that theory and if it proves to be the problem, I may be back in the forum looking for a work-around.

It is not an issue of having strings in the search. Find doesn't work with dates - at least as I can find in my search. So I wrote my own function to emulate it.

Function FindDate(rng As Range, DateValue As Date) As Range
Dim cl As Range

For Each cl In rng
    If cl.Value = DateValue Then
        Set FindDate = cl
        Exit Function
    End If
Next cl
FindDate = Nothing
End Function

Find does work with dates but it can be very temperamental, that's basically why I suggested using Application.Match and that's what I tend to use too, especially when the ultimate aim is to find a column/row.

As for handling the value not being found with Application.Match, try something like this.

Dim Res As Variant

    ' other code

    Res = Application.Match(cld.Offset(3,0).Value2, shs.Cells(homeRow).resize(,52),0)

    If IsError(Res) Then
        MsgBox cld.Offset(3,0).Value2 " not found."
    Else
        MsgBox cld.Offset(3,0).Value2 & " is in column " & Res
    End If.

Obviously that's an example and uses message boxes for illustration purposes.

The main thing is that you declare the variable that takes the result from Application.Match as Variant which to allow it to accept error values.

When Application.Match (or any other worksheet function) fails it returns an error value, so we can check for that using IsError.