Find a cell containing a specific date

Question

I have a excel serving as timeline record. Column B contains date of a year, and columns right on it record various event.
I want to make a button which can jump to the row of current date. The first thing I try to do is find a cell with specific date in it. I get a date from a existing cell in column B, then turn back to find it. However the Find method returns nothing.
Sub gotoToday()

Dim LDate As Date
Dim dateCol As Range
Dim cell As Range

LDate = Range("b197").Value ' do get a valid date value here

Set dataCol = Range("B2:B365") ' b197 is inside the range

dataCol.Select

Set cell = Selection.Find(what:=LDate, after:=ActiveCell, LookIn:=xlFormulas, _
    Lookat:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

cell.Select ' cell get Nothing here

End Sub

Basically I am following This stackoverflow post. Not sure what thing I am missing, can any one help me out?

Excelosaurus · Answer

My guess is that you are looking for a Date type (Dim LDate As Date) whereas dataCol contains strings. Try looking for a string:
Dim LDate As String
'...
LDate = Range("b197").Text
'...

EDIT
You can get a string from a date, formatted to your liking, by using the Format function. Example:
LDate = Format(Range("b197").Value, "mm/dd/yyyy")

If your values in column B really are dates, computed using formulas and formatted as mm/dd, the Find will work with LDate as a string and LookIn:=xlValues, even if they are displayed without the year.

Find a cell containing a specific date

One Answer

Add your own answers!

Ask a Question