Cells.Find(Date) not finding date from formula

[nic83]

I have a worksheet with a list of dates down column A that are automatically calculated. As this list is fairly long I need to find a way to open the work sheet and show the cell with todays date in.

I have the below macro that will find the date but only if i type the the dates in manually - it doesn't work on the formulas.

Private Sub Workbook_Open()
Range("A1").Select
Dim xrange As Range
Set xrange = Cells.Find(Date)
If xrange Is Nothing Then
MsgBox "Did not find any cell with today's date"
Else
xrange.Select
End If
End Sub


Any help would be great.

 

[sanrv1f]

Hello and welcome to the board,

When you specify a range in a workbook level module, you need to qualify it with the sheet name where it resides,

Private Sub Workbook_Open()
Dim xrange As Range
Set xrange = Sheets("Sheet7").Cells.Find(Date)
If xrange Is Nothing Then
    MsgBox "Did not find any cell with today's date"
Else
    xrange.Select
End If
End Sub

 

[nic83]

Thanks for this.

However I am still getting the 'Did not find any cells with todays date' message if I don't manually enter todays date somewhere in column A.

 

[amir_urmila]

Hi this because may basically you are putting hard coded value & you are finding it in formulas & not in values

you can put formula instead of hard coded date as below.

Private Sub Workbook_Open()
Range("A1").Select
Dim xrange As Range
Set xrange = Cells.Find(What:="=today()", LookIn:=xlFormulas, _
LookAt:=xlWhole)
If xrange Is Nothing Then
MsgBox "Did not find any cell with today's date"
Else
xrange.Select
End If
End Sub

 

[nic83]

This is excellent thank you.

One small thing, the formula I am using in not =Today(). In the list I have the dates:

<TABLE borderColor=#000000 cellSpacing=0 cellPadding=2 width=65 border=1><TBODY><TR><TD height=16>

21/09/09​

​

​

</TD></TR><TR><TD height=16>

22/09/09​

​

​

</TD></TR><TR><TD height=16>

23/09/09​

​

​

</TD></TR><TR><TD height=16>

24/09/09​

​

​

</TD></TR><TR><TD height=16>

25/09/09​

​

​

</TD></TR><TR><TD height=16>

26/09/09​

​

​

</TD></TR><TR><TD height=16>

27/09/09​

​

​

</TD></TR><TR><TD height=16>

28/09/09​

​

​

</TD></TR></TBODY></TABLE>

The formulas are:
<TABLE borderColor=#000000 cellSpacing=0 cellPadding=2 width=130 border=1><TBODY><TR><TD height=16>

=A16+1​

​

​

</TD></TR><TR><TD height=16>

=A17+1​

​

​

</TD></TR><TR><TD height=16>

=A18+1​

​

​

</TD></TR><TR><TD height=16>

=A19+1​

​

​

</TD></TR><TR><TD height=16>

=A20+1​

​

​

</TD></TR><TR><TD height=16>

=A21+1​

​

​

</TD></TR><TR><TD height=16>

=A22+1​

​

​

</TD></TR><TR><TD height=16>

=A23+1​

​

​

</TD></TR></TBODY></TABLE>

Is it possible to adapt your code to find todays date from this list?

 

[Weaver]

What if you look for the numerical value of the day, but make sure find formats is active?