Coded Search in Excel 2002 not working

i have the following code in a module:

Code:

---

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

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

Quote:

---

Originally Posted by 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

---

yep,

tried this as well:

Code:

---

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..

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

Quote:

---

Originally Posted by RobDog888

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:

Code:

---

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

---

doesnt work

Code:

---

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:

Code:

---

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

---

also:

Code:

---

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