Range.Find function in Excel VBA not able to find row

Question

My VBA subroutine can't seem to find a row with a matching date.

My Objective: To find the row number of the first row in Sheet1 with a date equal to the oldest date in Sheet2

My process is:

Find the row in Sheet2 with the oldest date
Find the first row in Sheet1 with the same date and return the row number of that row.

What Happens

When I run my sub I get is Run-time error '91' - Object variable or With block variable not set. If I try "Msgbox FindRow Is Nothing" I get the true response.

This makes no sense, since in this system Sheet1 E6 has the same value as Sheet2 E8

MY DATA:

Sheet1 (E2 - E8):
    09/10/2013
    09/10/2013
    14/11/2013
    14/11/2013
    17/11/2013
    17/11/2013
    20/11/2013

Sheet2 (E2 - E8):
    01/12/2013
    01/12/2013
    27/11/2013
    27/11/2013
    24/11/2013
    24/11/2013
    20/11/2013
    17/11/2013

My code so far:

Private Sub transferPostings()
    Dim EarliesNewDate As Variant
    Dim FirsCandidateOverlappingRow, rowCountHist, onlyNewRowsCount As Integer
    Dim wsHist, wsNew As Worksheet
    Dim possibleDuplicates, onlyNew, FindRow As Range

    Set wsHist = Worksheets("Sheet1")
    Set wsNew = Worksheets("Sheet2")

    wsNew.Range("E1").End(xlDown).NumberFormat = "General"
    Range(wsHist.Range("E2"), wsHist.Range("E2").End(xlDown)).NumberFormat = "General"

    EarliesNewDate = wsNew.Range("E1").End(xlDown).Value
    Set FindRow = Range(wsHist.Range("E2"), wsHist.Range("E2").End(xlDown)).Find(EarliesNewDate)

    wsNew.Range("E1").End(xlDown).NumberFormat = "dd/mm/yyyy"
    Range(wsHist.Range("E2"), wsHist.Range("E2").End(xlDown)).NumberFormat = "dd/mm/yyyy"
    MsgBox FindRow.Row
End Sub

Any help with this problem is much appreciated.

What are your international settings? With my US settings your routine works as designed. Are all of the date values really dates? Or are some of them text representations of dates? — Ron Rosenfeld
It seems that "EarliesNewDate" was declared as "Date" in my local code. When declaring it as double the code worked for me too. — MagnusN

Unknown Unknown · Accepted Answer · 2014-10-13T02:25:46

Your .Find command is not supplying any parameters. This means that it is using the parameters that were last used (or the defaults if it wasn't used in this session). At a minimum, you should be using LookIn:=xlValues and you should probably be specifying LookAt:=xlWhole.

Sub transferPostings()
    Dim EarliesNewDate As Variant
    Dim FirsCandidateOverlappingRow, rowCountHist, onlyNewRowsCount As Integer
    Dim wsHist As Worksheet, wsNew As Worksheet
    Dim possibleDuplicates, onlyNew, FindRow As Range

    Set wsHist = Worksheets("Sheet1")
    Set wsNew = Worksheets("Sheet2")

    wsNew.Range("E1").End(xlDown).NumberFormat = "General"
    Range(wsHist.Range("E2"), wsHist.Range("E2").End(xlDown)).NumberFormat = "General"

    EarliesNewDate = wsNew.Range("E1").End(xlDown).Value
    Set FindRow = wsHist.Range(wsHist.Range("E2"), wsHist.Range("E2").End(xlDown)).Find(What:=EarliesNewDate, LookIn:=xlValues, LookAt:=xlWhole)

    wsNew.Range("E1").End(xlDown).NumberFormat = "dd/mm/yyyy"
    Range(wsHist.Range("E2"), wsHist.Range("E2").End(xlDown)).NumberFormat = "dd/mm/yyyy"
    MsgBox FindRow.Row
End Sub

You didn't mention that the dates in Sheet1!E:E were the results of formulas. I strongly suspect they are and that would have been worth mentioning.

BTW, when declared as Dim wsHist, wsNew As Worksheet, wsHist is a variant not a worksheet. It doesn't matter in this case but you should be aware of how variables are declared for future practise. see Declaring Variables or Declaring Variables for more details.

Range.Find function in Excel VBA not able to find row

1 Answers