find a calculated date in a range

[teylyn]

OK, now I'm asking:

I want to find a date in a range. Using this code from Ron de Bruin's site works just fine.

If you have date's in column A then this example will select the cell with today's date.

source: http://www.rondebruin.nl/find.htm#select

Sub Find_Todays_Date()
    Dim FindString As Date
    Dim Rng As Range
    FindString = CLng(Date)
    With Sheets("Sheet1").Range("A:A")
        Set Rng = .Find(What:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlFormulas, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
            Application.Goto Rng, True
        Else
            MsgBox "Nothing found"
        End If
    End With
End Sub

But only if the dates have been manually entered. If I have a list of dates with a manually entered date in A1 and then =A1+1 in A2, copied down, the code tells me "Nothing found".

I've tried changing the LookIn: to xlValues, but that did not help.

What am I not getting? Is the date format important?

holding my breath...

[Palmetto]

See comments in the code

Option Explicit

Sub Find_Todays_Date()
    Dim FindString As Date
    Dim Rng As Range
    FindString = CLng(Date)
    With Sheets("Sheet1").Range("A:A")
        Set Rng = .Find(What:=FindString, _
                        After:=.Cells(1, 1), _ 'changed here
                        LookIn:=xlValues, _ 'changed here
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
            Application.Goto Rng, True
        Else
            MsgBox "Nothing found"
        End If
    End With
End Sub

[teylyn]

Nice! Thank you! Am I right in assuming that the problem lies with the different cell contents? i.e. the first cell is a value and the next few cells are formulas. Seems that the first cell content determines what is being looked for. Is that it?

How does one know these things? They're certainly not in the help files!!

cheers

[teylyn]

Hmmm, now comes the But:

Format does seem to matter. If the list of dates shows in the format 24/05/2010, the date will be found. With the format 24-May-2010 it will not be found.

What gives?

[teylyn]

OK,

riddle me this:

While Palmetto's suggestion fixed the find, it works only on specific formats. See attached.

Macro FindInA works fine on column A, which is formatted with the first Date format in the Date category, the option with the *, which means that it takes the OS regional settings into account.

Macro FindInC does not work on column C, which is formatted with the second Date format in the Date category, which also has the * asterisk.

Macro FindInE does not work on column E, which looks exactly the same as column A (at least with my regional settings. The column is formatted to dd/mm/yyyy)

Why is the date format so important, even though the parameter LookIn:=xlValues is set?

Or is this something that I'm only seeing because my locale settings are not US?

Any comments welcome.

[pike]

for column E range format as eg .. 14/3/2001

Sub FindInE()
    Dim FindString As String, FindString1 As Date
    Dim Rng As Range
    FindString1 = CLng(Date)
    FindString = FindString1
    With Sheets("Sheet1").Range("E:E")
        Set Rng = .Find(What:=FindString, _
                        After:=.Cells(1, 1), _
                        LookIn:=xlValues, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
            Application.Goto Rng, True
        Else
            MsgBox "Nothing found"
        End If
    End With

End Sub

[pike]

column c format eg friday, 21 may 2101

Sub FindInC()
     Dim FindString As String, FindString1 As Date
    Dim Rng As Range
    FindString1 = CLng(Date)
    Debug.Print FindString1
    
    FindString = Format(FindString1, "dddd, d mmmm yyyy")
    Debug.Print FindString
    With Sheets("Sheet1").Range("C:C")
        Set Rng = .Find(What:=FindString, _
                        After:=.Cells(1, 1), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
        If Not Rng Is Nothing Then
            Application.Goto Rng, True
        Else
            MsgBox "Nothing found"
        End If
    End With

End Sub

[pike]

Hi teylyn,
The code added and cells formated

[Palmetto]

Why is the date format so important, even though the parameter LookIn:=xlValues is set?

Or is this something that I'm only seeing because my locale settings are not US?

Inexplicably, I am getting the same results, even with including SearchFormat:=False in the code. The date format should make no difference since dates are serial numbers and we're looking in values. (Using =A1 [ or C1, E1] in another cell and setting it to general format, they all all return the same date serial, which shows the underlying value is the same in all three columns)

I've tried a number of approaches and none of them have been successful. Ideas anyone?

@Pike
Using your sample workbook, the code is still not finding the current Date in columns C and E.

[pike]

wow .....it is in mine so must be a local setting

[pike]

its 2am and I'm off to the beach to go fishing
but in the code have a look at the debiug,print value in immediate window and change the cell formats and =format(date,dd,d,mm,yyyy) to suit

[teylyn]

Basically that means that if the user changes the date format, the macro will bomb.

The date can be formatted whatever way, if the cell holds the manually entered date, using LookIn:=xlFormulas. Then the date serial will be the basis of the Find.

If the date is calculated, the xlFormulas does not cut it, because the formula is =A1+1 instead of the date serial. So now the Find has to go after a string, and the string has to match the format of the dates.

Not ideal.

Thanks all. Learned something.

[pike]

Not ideal
but since find is so fast you have time to seach all the different date formats

[teylyn]

Might be faster to query the date format of the first cell in the range and then model the search string after that.

If I knew how to do this, that is. I'm sure I'll find out when my eyesight returns.

[DonkeyOte]

I had a look at this yesterday and FWIW concur that unless the date format used on the sheet is the standard short format of that locale the Find would appear to fail.

I confess I rarely use Find for dates but appreciate that for matrices it's the most obvious way.

I don't have an answer for you I'm afraid but I would make the point that unless you can be sure all cells within the column share the same format - basing the criteria on the format of the first cell remains risky.

I would add that if you're using a Vector for the search range then you could as easily use Match (using Long as criteria).
The Match of Long will return the appropriate row/column etc irrespective of cell formatting.
(use Application.Match and store result as Variant if you wish to cater for errors without handler - ie IsNumeric test against result).

[teylyn]

Thanks DO,

I came from a MATCH angle and then got pointed to FIND as an alternative. With all the kerfuffle around date formats coming into the equation (and also, yes, I cannot assume that the first cell is representative of the format of the other cells in the column), I think I'll go back to MATCH.

Sometimes I feel that VBA and I don't marry well. Every time I venture into a new topic, I seem to hit a sore spot.

Thanks all for your help.