excel vba range.find not finding date

Question

I'm trying to establish the minimum date and its associated row for later use in a subroutine.

I'm hitting an error and i've spent the last few hours isolating the issue and searching for the solution to no avail.

I have a spreadsheet where column B contains a range of dates arranged in order. I can find the minimum date (the message box correctly returns 11/14/2015), but when I try use that date value to identify the row number I get error 91. Here is my code:

    Sub testing()

    ThisWorkbook.Worksheets("Burn Curve Data").Activate

    Dim rDateColumn As Range
    Dim dMinDate As Date
        Set rDateColumn = ActiveSheet.Range("B:B")
        dMinDate = Application.WorksheetFunction.Min(rDateColumn)

    MsgBox dMinDate

    Dim rMinCell As Range
    Dim intMinRow As Integer
        Set rMinCell = rDateColumn.Find(dMinDate, LookIn:=xlValues, LookAt:=xlWhole)
        intMinRow = rMinCell.Row

    MsgBox intMinRow

    End Sub

I tried inserting an If Not statement after Set rMinCell and determined that range.find is not finding the date. Here is the statement I used to identify the error, but I deleted it to clean up the code for this posting.

    If Not rMinCell is Nothing Then
        intMinRow = rMinCell.Row
    Else
        MsgBox "error finding rMinCell"
    End If

I also tried re-saving dMinDate into a string then using that string in the range.find but I encountered the same error of not finding the date.

Another nuance that may or may not be relevant is that this data exists within a named range on the worksheet. What am I doing wrong with my range.find line?!?

If the dates are arranged in order, why bother finding the min and searching for it at all? If you know the order of the dates, just take the first or last cell in the range, depending on how it is sorted — Soulfire
– Soulfire, Commented Aug 17, 2015 at 15:39
there are cells with other information above and below these dates in column B. i will also be using dMinDate as a default value in a userform. users will use this form when they want to change the beginning and end dates of this date range. people here can't be relied upon to properly copy/insert new lines with formulas in them. — J Hz
– J Hz, Commented Aug 17, 2015 at 15:41
No problem. Are the dates in column B properly formatted as dates (right click --> Format Cells and ensure 'Date' is selected under Category) ? I did a quick test and if the formatting wasn't a date I got the error 91 that you are getting. You might add the code rDateColumn.NumberFormat = "m/d/yyyy" after setting the variable and seeing if that helps. — Soulfire
– Soulfire, Commented Aug 17, 2015 at 15:42
If you get error 91 instead of getting the message provided by your errorhandler... that means you're actually entering the ` If Not rMinCell is Nothing Then intMinRow = rMinCell.Row ` ? Am I correct in this assumption? — David G
– David G, Commented Aug 17, 2015 at 15:49

Soulfire · Accepted Answer · 2015-08-17 19:43:14Z

2

As discussed in the comments it appears the issue arises when the column is formatted as something other than Date.

To do this via code you can add this line after you set the rDateColumn variable:

rDateColumn.NumberFormat = "m/d/yyyy"

This should format the column as a date and your Find method should work appropriately.

Alternatively you can select the column, click 'Format Cells' and then ensure 'Date' is selected under Category.

edited Aug 17, 2015 at 19:43

answered Aug 17, 2015 at 16:31

Soulfire

4,3161 gold badge25 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

excel vba range.find not finding date

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related