Forum Discussion
Finding a date on another sheet.
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
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 Replies
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 SubI 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.
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.
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
