Coded Search in Excel 2002 not working

[Br1an_g]

i have the following code in a module:

Dim StartDate as Date

ActiveSheet.Range("A1").Select

Activesheet.Cells.Find(What:=StartDate, After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

StartDate = 17/01/05 (variable taken from another sheet)

when i manually search the sheet, it finds the above entry, but when i use the code, it fails with 'Object variable or With block variable not set'

i have tried formating the date as dd/mm/yyyy, but still not working. I have a feeling it is to do with formats, but not sure, as it will find a string, just not this date.

I have also tried recording the same search via excel, but it still wont work.

i found this on MSKB: Run Time Error 91

could it be something to do with this?

any ideas?

TIA

Brian

[RobDog888]

It could be that .Find is not evaluating the variable contents, but instead
searching for the literal text? Test it out by hardcoding the date in the
What:= parameter.

HTH

[Br1an_g]

It could be that .Find is not evaluating the variable contents, but instead
searching for the literal text? Test it out by hardcoding the date in the
What:= parameter.

HTH

yep,

tried this as well:

Activesheet.Cells.Find(What:="17/01/05", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

still doesnt work, and same error..

[RobDog888]

If the column is formatted as a Date then try passing a search criteria with
the '#' character surrounding the date value.

[Br1an_g]

If the column is formatted as a Date then try passing a search criteria with
the '#' character surrounding the date value.

I had thought of this as well.

heres a couple of things i have tried:

Cells.Find(What:="#" & StartDate & "#", After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

doesnt work

Cells.Find(What:=#StartDate#, After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

It doesnt like this code

or this:

Cells.Find(What:='#StartDate#', After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

also:

StartDate = "#" & StartDate & "#"

prior to the search (had to declare the variable as a variant)

I cant think how else to do this!

I have also tried declaring the StartDate variable as a variant, and string and it still doesnt work. variant gives same error, and string doesnt work with other segments of my code so doesnt get this far.

thanks