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?
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
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP