Finding Dates with different formats

[mribadeneira]

I am having a problem using Find with VBA to look for dates in a database. The database has the dates in the DD/MM/YYYY format.
The users then select a date they want to find from a dropdown list in a Userform. The dropdown list is made from the database but the format changes to M/D/YYYY. This makes the dates from the userform not match the dates from the database so Find does not work. How can I make VBA shearch for the date regardless of the format?

I have tried playing with it to see if it works but it is still not working. I made the date format in the database match the format in the userform and I made the date selected a String variable. I am using the code:

Set c = .Find(what:=DateValue(fecha), LookIn:=xlValues)

</pre>
to find it but it only works with some of the dates, most still not working.
It usually works with dates that have two values for day and two values for month (11/10/2012) but not if there is only one value in day or month (5/9/2012).

 

[Jerry Sullivan]

Hi mribadeneira,

Using Range.Find does a Text search which doesn't work well for dates that are not formatted the same.

If the drop down and database are both formatted "DD/MM/YYYY" as it sounds like you tried, then this should work...

Set c = .Find(what:=fecha, LookIn:=xlValues)

Note that code doesn't use the DateValue function which converts "DD/MM/YYYY" to the default date format on your system.


Another alternative is to use the Application.Match function using the Value2 Property of the Cell that holds your drop down selected date. That will find the first numeric match regardless of the number formatting. The downside of Match is that is not well suited for multiple matches (like Range.FindNext) or searching ranges that are not single column or single row lists.