Find Method to Find Dates
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)
'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
See also:
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.