Find Method to Find Dates

< Back to Search results

 Category: [Excel]  Demo Available 

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)


    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

See also:

Excel: Get Text From Excel Cell Comments
Excel: Get Data From Closed Excel Workbooks
Excel: Get Last Word From Text String
Extract Number From Text String
Excel VBA: Extract Word From a Text String
Excel VBA: Find Number Between 2 Numbers


See also Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions.


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


stars (0 Reviews)