<<Convert Excel Spreadsheets to Web Pages \| Trading Software That Operates Within Excel \| Convert Excel, Access & Other Databases \| Merge Excel Files>>

Find Method to Find Dates

| | Information Helpful? Why Not Donate.

TRY OUT: Smart-VBA | Code-VBA | Analyzer-XL | Downloader-XL | Trader-XL| More Free Downloads.. Best Value: Finance Templates Bundle

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

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 StringDim rCell As RangeDim 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 IfEnd 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. ALLpurchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.

Instant Download and Money Back Guarantee on Most Software

Try out:Analyzer XL |Downloader XL |Smart VBA |Trader XL Pro (best value) |ConsoXL | MergeXL | O2OLAP for Excel | MORE>>

Excel Trader PackageTechnical 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