Yes, I am using Excel 2007.
One thing that has become clear is that the .Value of a cell is more than the numeric value: it can contain information regarding the formatting of the value, and this formatting information is checked, along with the numeric value, by .Find. Both have to match.
If I enter .1234567 in D1 and format it as a Date, and I then do Range("D2").Value = Range("D1").Value, the format of D2 changes from General to Custom h:mm:ss AM/PM and I see: 2:57:47 AM. This formatting information allows the .Find method to match a Time formatted cell (but only those that are similar, not all Time formats).
However, if I do the same thing with D1 formatted as Time, the format in D2 stays as General (assuming that it was set to General to begin with) and I see in D2: 0.1234567, i.e. for some strange(?) reason .Value does not in this case include any information regarding the Time format, so .Find will not match it to a Time formatted cell.
Then it hit me. Instead of formatting D1 as a Date, I should be able to use your code with one minor modification:
This way the match should be independent of the actual format of D1. This worked!! I can use either xlValues or xlFormulas, and the format of D1 can be General, Time, Date etc.
Bookmarks