VBA & Macro Problems

How to Fix VBA Find Method Not Locating Dates in Excel

Natalie Taylor Oct 22, 2026 868 views

Question details

The user is attempting to find a specific date, such as an Outlook appointment end date, using an Excel VBA macro. However, the Find method fails to locate the date in the worksheet, even though it appears to exist visually.

How to Fix VBA Find Method Not Locating Dates in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Searching for a specific date value within an Excel worksheet range using a VBA macro's Find method.
Observed behavior: The VBA Find method fails to return a matching cell (returns Nothing) because of mismatches between underlying date serial values, text formatting, or custom display formats.

Before you start

Ensure that the worksheet cells you are searching through contain actual Excel date serial values and have not been accidentally stored or formatted as plain text strings.

Solution 1Recommended

Use LookIn:=xlValues and Verify Cell Date Formats

Ensure the target cells contain valid Excel date serial numbers and update your Find method parameters to search by evaluated value rather than formula.

Excel stores dates as numerical serial values. A common reason the VBA Find method fails is that it attempts to match a formatted text string against these serial numbers, or the target cells are formatted as text while the code searches for a date object.

Custom display formats only change how a date appears visually on the spreadsheet; they do not alter the underlying date serial value that VBA needs to locate.

Check Cell Formatting

Select the cells containing the dates (for example, A5:NB5) and press Ctrl+1 to open the Format Cells dialog.

Ensure Proper Date Type

Verify that the cells are formatted as 'Date' or 'General', rather than 'Text'. If they are text, convert them to dates so Excel recognizes them properly.

Update VBA Find Method Arguments

In your VBA macro, explicitly add the parameter LookIn:=xlValues to your Find method. This forces the search to look at the evaluated result. Example: Range("A5:NB5").Find(What:=DateValue, LookIn:=xlValues).

Use LookIn:=xlValues and Verify Cell Date Formats

Data Type Matching: Make sure the variable you are passing to the 'What' argument in the Find method is properly declared as a Date or converted to match Excel's internal storage.

Solution 2

Convert the Search Date to a Double Data Type

If searching directly with a Date object fails, converting the target date to a Double ensures VBA searches for the exact numerical serial number Excel uses.

Free Microsoft Office alternative

Experience Seamless Data Management with WPS Office

If you are tired of troubleshooting complex VBA macros and date formatting issues, WPS Office provides a lightweight, highly compatible, and user-friendly alternative to Microsoft Office. It supports advanced spreadsheet functions and extensive format compatibility without the heavy processing demands.

1. Download and Install: Visit the official WPS Office website to download the free installation package for your operating system.
2. Open Your Spreadsheets: Launch WPS Spreadsheet and seamlessly open your existing Excel workbooks without losing data or formatting.
3. Work with Familiar Tools: Continue processing your data using a familiar tabbed interface that requires zero learning curve.

Fully compatible with Microsoft Excel file formats (.xlsx, .xls, .csv).Built-in support for advanced formulas and seamless data formatting.Lightweight software that installs quickly and runs smoothly on older devices.Free to download and use for your daily spreadsheet and office tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does the VBA Find method return 'Nothing' when searching for a date?

This usually happens because Excel stores dates as numerical serial numbers (e.g., 44197) but displays them based on cell formatting. If your Find method searches for a formatted text string or evaluates formulas instead of values, it will not match the underlying serial number.

Does changing the custom cell format affect the VBA Find method?

Changing a custom display format (such as altering MM/DD/YYYY to DD-MMM) does not change the underlying date serial value. However, if the format is changed to 'Text' and the date is re-entered, the underlying value changes from a serial number to a text string, which requires a different search approach in VBA.

How do I force the VBA Find method to look at the displayed value instead of the formula?

You can specify LookIn:=xlValues in your Find method arguments. This explicitly tells VBA to search the evaluated result (what you actually see in the cell) rather than the underlying formula used to calculate it.

Excel & Data Problems