search for combined time and date

[davidlindsey]

Hi

i need to search for a combined specific time and date value in a single column, as shown:
28-02-2018 17:00
28-02-2018 18:00
28-02-2018 19:00
28-02-2018 20:00
28-02-2018 21:00
etc

if i omit the time, I can search for a day, without issue using :

d = DateSerial(Year(cd), Month(cd), Day(cd))
Set r = Cells.Find(What:=d, LookIn:=xlValues, LookAt:=xlWhole, After:=Range("a1"))

but cannot successfuly include a time aspect (d + TimeSerial(Hour(d), Minute(d), 0) - the search just fails to find anything. I am presuming this type of search must be a relatively common need: appeciate if anyone can point me to a method that can work.

thanks..

[AlphaFrog]

The .Find method is a text based search. Format your search for date-time the same as the displayed date-times in the column

Please Login or Register  to view this content.

[davidlindsey]

thanks for your idea, but that still didn't work for me - so I changed your code to refer specificially search one of the cells in the column

Set r = Cells.Find(What:=Format(Range("A10"), "dd-mm-yy hh:mm"), LookIn:=xlValues, LookAt:=xlWhole, After:=Range("a1"))

and it still wouldn't find it.


I can do a simple "find" in Excel (not using VBA) and it will find the correct date/time entry in the column. If I record the process and run that - it doesnt find it ???????

[jindon]

Just try change to

Please Login or Register  to view this content.

and see if it works.

[AlphaFrog]

Try this...

Please Login or Register  to view this content.