.Find method usage on Time Values advice?

In a recent post, I was asking for advice about the fastest method to find values in a range using VBA, and came across sites that indicated the Range.Find method was hundreds of times faster than looping. So I gave it a go, and it works fine for me using the below code on integers. It does not work on time however, which is what I do need. There seems to be scant data on using this method for searching times, and some data regarding dates. Does anyone have any thoughts on this?

The attached workbook has the below code and data in Sheet 1 as per the screen shot for testing.

If you modify the code below from ".Range("f6:f10")" to ".Range("c6:c10"), and "myRangeTimes" to "myRangeIntegers", then you will be able to witness the Find method working with the corresponding integer values 1-5 in Sheet1. Using this same code on the time values as is shown below, returns the wrong result from the Find, setting the user entry "12:00:00 AM" cell interior color with the result from "10:00:00 AM" in the myRangeTimes range. I suspected the format had something to do with this, so I copied and pasted the user input cells to the myRangeTimes range to ensure there was no differences, but the problem persists.

Please Login or Register  to view this content.

Thanks in advance for any advice folks,
Frank

I have found it helps to search for a string formatted the same way as the time in the cells you are searching. Try this. I've tested it.

Please Login or Register  to view this content.

well this is pretty quirky, no doubt. see here:

https://www.ozgrid.com/VBA/find-dates.htm

the range cell is being read like numbers. furthermore, you have no args in your method. this should never be:

Please Login or Register  to view this content.

but rather it should be, at the very least:

Please Login or Register  to view this content.

then, you should test all of these:

https://docs.microsoft.com/en-us/off...l.xlfindlookin

this gives nothing:

Please Login or Register  to view this content.

this also gives nothing:

Please Login or Register  to view this content.

however, this DOES give something:

Please Login or Register  to view this content.

and so does this:

Please Login or Register  to view this content.

trial and error my friend. test, test, test. with software running rampant all over the world, WHO KNOWS? LOL.

you have no args in your method. this should never be:

You don't need to put the name of the argument.

Originally Posted by Fluff13

You don't need to put the name of the argument.

probably not. but everyone I've seen use it, does it.