CODEWITHSUNDEEP
exceldatefindruntime-errorvba
J Hz
J Hz

Reputation: 35

excel vba range.find not finding date

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?!?

Upvotes: 3

Views: 3566

Answers (1)

Soulfire
Soulfire

Reputation: 4296

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.

Upvotes: 2

Related Questions