Find returns nothing even if value exists

Question

So here I have the nth question about the find method. I have read many questions about it and the problems it carries, but still couldnt find a solution to my problem.

I simply want to return the number of row and number of column for a particular value (a date). However, the code runs always the same 91 error (object variable not set) because the find method does not find anything.

I have tried to define the variable as range and change the code by setting the variable (i.e. set daterow = etc.). But the problem persists.

Sub actual_cash_flow()
Dim cfdate As Long
Dim today As Long
Dim daterow As Long
Dim datecolumn As Long

today = Date
cfdate = WorksheetFunction.EoMonth(today, -1)

daterow = Sheet2.Cells.Find(What:=cfdate, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, searchformat:=False).Row
datecolumn = Sheet2.Cells.Find(What:=cfdate).Column


End Sub

I would like to have number of row and number of column in order to identify the cell and then perform some actions.

EDIT:

By editing the code as suggested by @mikku and by debugging the range where there is the value and the variable defined in the code I get the same value, however, I still get nothing as output. So I really dont know where the error is. See the image.

Answer 1

As I pointed out in my answer to a previous question, working with dates and the Range.Find function can be tricky. One of the reasons seems to be that the VBA Date datatype is not the same as the date stored on an Excel worksheet. The latter is a Double formatted to look like a date.

So, especially if you want your .Find method to be independent of the date settings, you are probably best off not using the Range.Find method but rather looping through the data.

In the code below, I show an example of how this might work, making assumptions based on the workbook you provided, and also using a VBA array as this will run much faster than looping through a range on the worksheet:

Sub actualcf()

Dim cfdate As Long  'Yes --Long for this application
Dim daterow As Long
Dim datecolumn As Long
Dim fnd As Range

Dim srchRng As Range
Dim vSrch As Variant
Dim I As Long

With Worksheets("Peschiera CF")

'Find the row with the dates
Set srchRng = .Cells.Find(what:="Yr. Ending", after:=.Cells(1, 1), LookIn:=xlValues)

'Read that row into a VBA array, but only the columns with data
'Note that we are using `.Value2` which has no formatting
If Not srchRng Is Nothing Then
    vSrch = .Range(.Cells(srchRng.Row, 1), .Cells(srchRng.Row, .Columns.Count).End(xlToLeft)).Value2
End If

cfdate = WorksheetFunction.EoMonth(Date, -1)
For I = 1 To UBound(vSrch, 2)
    If vSrch(1, I) = cfdate Then
        daterow = srchRng.Row
        datecolumn = I
    End If
Next I

End With
End Sub

Answer 2

This code should work:

Sub actual_cash_flow()

Dim cfdate As Date
Dim daterow As Long
Dim datecolumn As Long
Dim fnd As Range

cfdate = WorksheetFunction.EoMonth(Date, -1)

Set fnd = Worksheets("SheetName").Cells.Find(What:=cfdate, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False, searchformat:=False)

If Not fnd Is Nothing Then

    daterow = fnd.Row
    datecolumn = fnd.Column

End If

End Sub

Put in your sheet Name in the Code.

The problem was with the setup of variables, you should declare them as date when searching for a Date.