TransWikia.com

Find a cell containing a specific date

Stack Overflow Asked on December 22, 2021

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?

One 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.

Answered by Excelosaurus on December 22, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP