Find Method to Find Dates

Excel VBA Find Method to Find Dates. Find Excel Dates in Excel

Excel VBA Find Method to Find Dates Excel Find Method in VBA | Advanced Excel Find |

The find method is an excellent method to use in Excel VBA macro code. It is many hundreds of times faster than a VBA Loop, which is often erroneously used to locate data. However, things become somewhat tricky when using the Find Method to Find Dates. Those that use a US date format do not have the problems that us outside the US encounter when using Find to locate dates. In other words, if your local date format is NOT mm/dd/yyyy (set in Windows) you can often run into problems.

Even recording a macro using Find to find a date will often bug out when it is played back. Normally the debug message is: "run time error '91': Object variable or With block variable not set." Yet you know the exact date exists on the Worksheet.

The trick is to ensure you use a true excel date and format it the same as your default Excel date format, set in Windows. Unless changed, this will be "Short Date" or "Long Date". Most often the former. Don't pay too much attention to how Excel displays the date on your Worksheet as it is only a displayed value. What we need is the true underlying value of date that Excel is using to store the date. To see this, select any cell that houses a date and then look in the Formula bar. This is what Excel stores the date as.

The Excel macro code below can be used to locate a date on the current worksheet and should show you how one can use the Find Method in Excel VBA to find dates. Note also the use of CDate conversion function.

Sub FindDate()

Dim strdate As String

Dim rCell As Range

Dim lReply As Long



    strdate = Application.InputBox(Prompt:="Enter a Date to Locate on This Worksheet", _

                Title:="DATE FIND", Default:=Format(Date, "Short Date"), Type:=1)



    'Cancelled

    If strdate = "False" Then Exit Sub



    strdate = Format(strdate, "Short Date")



    On Error Resume Next

        Set rCell = Cells.Find(What:=CDate(strdate), After:=Range("A1"), LookIn:=xlFormulas _

            , LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

    On Error GoTo 0



    If rCell Is Nothing Then

        lReply = MsgBox("Date cannot be found. Try Again", vbYesNo)

        If lReply = vbYes Then Run "FindDate":

    End If

End Sub