Find Date in Excel using VBA 2

[sdiverdan]

cannot find a date using "Find" in excel. I have dates in the first column in format "mm/dd/yyyy" and need to find the row the date is in. I'm using the following lines and the result is the date is not found. I tried using the 11/12/2003 as a number, a string,as a variable and even as the number 37937 however its not found. If I search on a number or string in a different column the code works.

With Worksheets(Sheet1).Range("a1:a100&quot
Set c = .Find("11/12/2003&quot 'find first occurrence

Please help
Dan




Dan

 

[TonyJollans]

Hi Dan,

Don't know what country you are in or what your date settings might be but it's worth trying "12/11/2003" as well as "11/12/2003".

Enjoy,
Tony

 

[sdiverdan]

Tried that too.

Dan

 

[xlbo]

ok - there are a coupla possible tripups here. 1st is that you usually should specify all the variables in find otherwise it will use the variables that were set last time the find was used. Also, dates can be hard to find - use the format in the column.....

With Worksheets(Sheet1)
Set c = .Range("a1:a100&quot.Find(format("11/12/2003",.range("A1&quot.numberformat)

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[BenyG]

sdiverdan, I had the same problem. The difficulty is that you *must* set the format using format(). See how it is done with the thread I started a while back:

thread707-597289


Hope this helps,

Ben

 

[Chattin]

May be useful to you - from Chip Pearson's site -

http://www.cpearson.com/excel/DateTimeVBA.htm

Dates In The Find Method

Using the .Find method to search for dates can be a bit tricky. Regardless of how the date is formatted to display (as long as it is a date format of some sort), you must search for it in its "standard" format, e.g., "7/18/1998" rather than "7/18/98" or "July 18, 1998". For example,

Set FoundCell = Range("A1:A100&quot.Find (what:="7/18/1998&quot

Alternatively, you can use the DateValue function to convert any date format into an Excel serial date, and search the formulas of the range (even though they are not formulas in the conventional sense) to find your date.

Set FoundCell = Range("A1:A100&quot.Find _
(what:=DateValue("July 18, 1998&quot ,lookin:=xlFormulas)



Chris ;-)

Visit

http://www.mrexcel.com/board/index.php

for great xl and xl/VBA help

 

[sdiverdan]

Thanks Benny and Chris, I gave you both a star. It works! Making the variable "as Date" and using the what: and lookin: suggestions worked. I love this forum. Thanks!:-D

Dan