Finding a date on another sheet.

[Sue]

Hi nice people,

In my application I compute a variable called EntryDate (of data type date) from a form with DateSerial(txtYear,txtMonth,txtDay). This (using an example) produces 01/04/2021, which is correct. It is then asked to see if this date is present on another sheet, using:

With Worksheets("OtherSheet").Range("WorkRange")

   Set MyEdit = .Find(EntryDate, , xlValues, xlWhole, xlByColumns, , True)

End With

It does not find it. It should because this date is present, in the first column (it will always be the first column as this is the date column).

I have used little test things like computing EntryDate as above and asking if this is equal to cell A2 and the answer is yes, true. I can show EntryDate= 01/04/2021 and cell A2 = 01/04/2021 although actually cell A2 is formatted as 01-04-2021.

I feel my problem may be related to date formatting.

Clever people, I would so like to know why the computer knows that A2 contains EntryDate but will not "find" it for me!

This is part of code I am writing to avoid adding accidental duplicates.

Sue

[#2 · Accepted solution]

@Sue 

This is a known problem. Excel VBA is very finicky when it comes to finding a date in a range.

Two workarounds:

1) If the dates in WorkRange are constants, not the result of formulas, change xlValues to xlFormulas:

Set MyEdit = .Find(EntryDate, , xlFormulas, xlWhole, xlByColumns, , True)

- or -

2) Leave xlValues alone, and specify the first argument formatted exactly as on the worksheet:

Set MyEdit = .Find(Format(EntryDate, "dd-mm-yyyy"), , xlValues, xlWhole, xlByColumns, , True)

or

Set MyEdit = .Find(Format(EntryDate, "mm-dd-yyyy"), , xlValues, xlWhole, xlByColumns, , True)

depending on which format the cells use.

[#3]

@Sue 

Here is an example with VBA.

Search simple and effective.

Example file and VBA code inserted.

'Insert into a module
Option Explicit
Sub Search()
Dim booGefunden As Boolean
Dim strEingabe As String
Dim rngFeld As Range
Dim wksSheet As Worksheet
strEingabe = Application.InputBox("Please enter search term", "Search", "Search term")
If strEingabe <> "" Then
    For Each wksSheet In ActiveWorkbook.Worksheets
        For Each rngFeld In wksSheet.UsedRange
            If InStr(UCase(CStr(rngFeld.Value)), UCase(strEingabe)) > 0 Then
                booGefunden = True
                wksSheet.Activate
                rngFeld.Select
                If MsgBox("Keep searching?", vbQuestion + vbYesNo) = vbNo Then Exit Sub
            End If
        Next rngFeld
    Next wksSheet
    If booGefunden = False Then MsgBox "'" & strEingabe & "' not found!", vbInformation
End If
End Sub

I would be happy to know if I could help.

NikolinoDE

I know I don't know anything (Socrates)

* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.

[#4]

@HansVogelaar 

Hi Hans

Thank you very much. I have tried both your suggestions and both find the required date in the other sheet when it is present - so I will be able to proceed with the programming.

I have also learnt something new. From the vba help on ".Find" I had no idea that the "LookIn" field given as "The type of information" (which did not mean anything in particular to me) could include such terms as xlFormulas which could equate different date formats! Sometimes the standard help facility just isn't helpful, or helpful enough!

I congratulate you on how clear and concise your help was to my problem and also helpful in understanding issues with dates in general.

Sue