Excel VBA Find Method to Find Dates <See Also: Excel Find Method in VBA | Advanced Excel Find | Excel Find & Replace>
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 erroronously 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/yyy (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: Excel Find Method in VBA | Advanced Excel Find | Excel Find & Replace>
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to firstname.lastname@example.org 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates