Searching for Dates in Column in VBA

Question

I am trying to search for a date in a column in VBA. I have verified that the value exists within the column and I have also formatted the date to be that of the dates in the reference column. Have been scratching my head and i'm not sure what i'm doing wrong. I've seen several questions related to this, and I have even mirrored one of the solutions, which has not worked.

Notes: 1) All dates are stored in column 1 (A) 2) Date format is "m/d/yyyy" 3) Sheet 13 is where the range lives 4) I also have a named range that contains the date i'm searching for and I access that range by referencing the cell rather than the named range since VBA doesn't seem to like that. Any thoughts?

 searchString = Format(Sheet3.Cells(5, 4).Value, "m/d/yyyy") With Sheet13.Range("A:A") Set searchCell = .Find(What:=CDate(searchString), After:=ActiveCell, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) End With

Are your dates in Sheet13.Range("A:A") stored as dates or text. Excel dates are actually numbers, not text. You seem to be trying a text match with a date field. — mooseman
– mooseman, Commented Aug 7, 2019 at 18:02

Shai Rado · Accepted Answer · 2019-08-07 18:14:49Z

0

You need to match the date (stored as number value in Excel):

Dim DateVal As Date DateVal = Sheet3.Cells(5, 4).Value With Sheet13.Range("A:A") Set searchCell = .Find(What:=DateVal, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows) End With

answered Aug 7, 2019 at 18:14

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

StormsEdge Over a year ago

Shai Rado the solution above throws a type mismatch exception when trying to execute the find statement.

Shai Rado Over a year ago

@StormsEdge did you define Dim searchCell as Range ?

StormsEdge Over a year ago

Yes. searchCell is a range and searchString is a date. It was a string when I first started. Neither appear to work

Ron Rosenfeld · Accepted Answer · 2019-08-07 20:14:08Z

Another example of how using references like ActiveAnything can lead to errors.

In your code, if ActiveCell doesn't happen to be in sheet13 column A, your code will error.

Change to After:=.Cells(1,1) or something similar so you ensure you are starting with some cell within in the column you are using as the SearchRange.

Collectives™ on Stack Overflow

Searching for Dates in Column in VBA

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related