find a calculated date in a range

OK, now I'm asking:

I want to find a date in a range. Using this code from Ron de Bruin's site works just fine.

If you have date's in column A then this example will select the cell with today's date.

source: http://www.rondebruin.nl/find.htm#select

Please Login or Register  to view this content.

But only if the dates have been manually entered. If I have a list of dates with a manually entered date in A1 and then =A1+1 in A2, copied down, the code tells me "Nothing found".

I've tried changing the LookIn: to xlValues, but that did not help.

What am I not getting? Is the date format important?

holding my breath...

See comments in the code

Please Login or Register  to view this content.

Nice! Thank you! Am I right in assuming that the problem lies with the different cell contents? i.e. the first cell is a value and the next few cells are formulas. Seems that the first cell content determines what is being looked for. Is that it?

How does one know these things? They're certainly not in the help files!!

cheers

Hmmm, now comes the But:

Format does seem to matter. If the list of dates shows in the format 24/05/2010, the date will be found. With the format 24-May-2010 it will not be found.

What gives?

OK,

riddle me this:

While Palmetto's suggestion fixed the find, it works only on specific formats. See attached.

Macro FindInA works fine on column A, which is formatted with the first Date format in the Date category, the option with the *, which means that it takes the OS regional settings into account.

Macro FindInC does not work on column C, which is formatted with the second Date format in the Date category, which also has the * asterisk.

Macro FindInE does not work on column E, which looks exactly the same as column A (at least with my regional settings. The column is formatted to dd/mm/yyyy)

Why is the date format so important, even though the parameter LookIn:=xlValues is set?

Or is this something that I'm only seeing because my locale settings are not US?

Any comments welcome.

for column E range format as eg .. 14/3/2001

Please Login or Register  to view this content.

column c format eg friday, 21 may 2101

Please Login or Register  to view this content.

Hi teylyn,
The code added and cells formated

Why is the date format so important, even though the parameter LookIn:=xlValues is set?

Or is this something that I'm only seeing because my locale settings are not US?

Inexplicably, I am getting the same results, even with including SearchFormat:=False in the code. The date format should make no difference since dates are serial numbers and we're looking in values. (Using =A1 [ or C1, E1] in another cell and setting it to general format, they all all return the same date serial, which shows the underlying value is the same in all three columns)

I've tried a number of approaches and none of them have been successful. Ideas anyone?

@Pike
Using your sample workbook, the code is still not finding the current Date in columns C and E.

wow .....it is in mine so must be a local setting

its 2am and I'm off to the beach to go fishing
but in the code have a look at the debiug,print value in immediate window and change the cell formats and =format(date,dd,d,mm,yyyy) to suit

Basically that means that if the user changes the date format, the macro will bomb.

The date can be formatted whatever way, if the cell holds the manually entered date, using LookIn:=xlFormulas. Then the date serial will be the basis of the Find.

If the date is calculated, the xlFormulas does not cut it, because the formula is =A1+1 instead of the date serial. So now the Find has to go after a string, and the string has to match the format of the dates.

Not ideal.

Thanks all. Learned something.

Not ideal
but since find is so fast you have time to seach all the different date formats

Might be faster to query the date format of the first cell in the range and then model the search string after that.

If I knew how to do this, that is. I'm sure I'll find out when my eyesight returns.

I had a look at this yesterday and FWIW concur that unless the date format used on the sheet is the standard short format of that locale the Find would appear to fail.

I confess I rarely use Find for dates but appreciate that for matrices it's the most obvious way.

I don't have an answer for you I'm afraid but I would make the point that unless you can be sure all cells within the column share the same format - basing the criteria on the format of the first cell remains risky.

I would add that if you're using a Vector for the search range then you could as easily use Match (using Long as criteria).
The Match of Long will return the appropriate row/column etc irrespective of cell formatting.
(use Application.Match and store result as Variant if you wish to cater for errors without handler - ie IsNumeric test against result).

Thanks DO,

I came from a MATCH angle and then got pointed to FIND as an alternative. With all the kerfuffle around date formats coming into the equation (and also, yes, I cannot assume that the first cell is representative of the format of the other cells in the column), I think I'll go back to MATCH.

Sometimes I feel that VBA and I don't marry well. Every time I venture into a new topic, I seem to hit a sore spot.

Thanks all for your help.