Excel VBA Problem- Using Find Function
 

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

--
Message posted from http://www.ExcelForum.com

Excel VBA Problem- Using Find Function
 

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.


"anandmr " wrote in message
...
Hi,

I am facing a peculiar problem. I using the following function in VB
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 works
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 date
value.

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

Thanks in advance

Regards
Anand


---
Message posted from http://www.ExcelForum.com/

Excel VBA Problem- Using Find Function
 

"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

--
Message posted from http://www.ExcelForum.com

Excel VBA Problem- Using Find Function
 

"Doug Bell" <dug@bigpond wrote ...

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.

--

Excel VBA Problem- Using Find Function
 

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.

--
Regards,
Tom Ogilvy

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


---
Message posted from http://www.ExcelForum.com/

Excel VBA Problem- Using Find Function
 

I see, thanks.

Tom Ogilvy wrote:
*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.

--
Regards,
Tom Ogilvy

--
Message posted from http://www.ExcelForum.com