Forum Discussion

Sue's avatar
Sue
Copper Contributor
Jul 16, 2021
Solved

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 i...
Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Jul 16, 2021

    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.

NikolinoDE's avatar
NikolinoDE
Gold Contributor
Jul 16, 2021

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.

Resources