Excel VBA Problem- Using Find Function

[anandmr]

Hi,

I am facing a peculiar problem. I using the following function in V
for finding a date in excel.

Selection.Find(What:=strCurrentDate, After:=ActiveCell
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns
SearchDirection:=xlNext, MatchCase:=False).Activate

The values will be passed to "strCurrentDate" dynamically.

When I pass values like "20-Jun-04" or "29-Jun-04" the function work
perfect without any errors, but when I pass values like
"01-Jul-04" or "02-Jul-04" then I get error as "Run time error 91
Object variable or block variable not set " .

I feel it has got something to do with the starting zero in the dat
value.

Could any of you help me out with a solution to over come thi
problem.

Thanks in advance

Regards
Anan

 

[Doug Bell]

Anand,
It could be something to do with the strange way Americans write dates ie
mm/dd/yy
I am not sure if Excel holds dates in the same format as Access but in
Access SQL to get the date to work properly you have to format the criteria
in the non international format.

Try formating your search date
strCurrentDate=format(datSearchDate,"mm/dd/yyyy")
or if it is the preceeding 0 try handling it before you search ie look for
it and remove it.

 

[Chris_Fifield]

"Find" produces that error when what you are searching for is not foun
on the spreadsheet. Are those values that are giving you the error o
your sheet? As Doug pointed out you may have to format your query, bu
also make sure what's on your spreadsheet is formatted properly so tha
it will match with your query

 

[Jamie Collins]

It could be something to do with the strange way Americans write dates ie
mm/dd/yy
I am not sure if Excel holds dates in the same format as Access but in
Access SQL to get the date to work properly you have to format the criteria
in the non international format.

Try formating your search date
strCurrentDate=format(datSearchDate,"mm/dd/yyyy")

Could be better to try an unambiguous format e.g. this one usually
works for me with Jet:

Format$(dtmTest, "dd mmm yyyy")

Jamie.

--

 

[Tom Ogilvy]

Find doesn't produce an error when the item searched for is not found. Code
that assumes the item will always be found produces the error

cells.Find("ZX2HZ")

doesn't produce an error

cells.Find("ZX2HZ").Activate

produces an error because it assumes the string is found.

 

[Chris_Fifield]

I see, thanks.

*Find doesn't produce an error when the item searched for is no
found. Code
that assumes the item will always be found produces the error

cells.Find("ZX2HZ")

doesn't produce an error

cells.Find("ZX2HZ").Activate

produces an error because it assumes the string is found.