Find Part of a date 1

[Molby]

Hi All,

I'm having real problems searching for the month and year part in a range of dates.

In column A I have a range of dates from 01/01/2003 to 01/06/2004 in that format (DD/MM/YYYY). I am trying to find the first occurance of a date that is greater than the 31/12/2003. I've recorded myself doing a find in excel which gives the following code, i.e. look for any date containing the month of January 2004:

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

This works fine while I'm recording it, and it goes to the desired cell, but my issue is when I run the code in stepping through I get a run-time error '91' Object variable or With Block variable not set.

What's causing this?

Thanks,

Ian

 

[Loomah]

Hi
Do you get the error on this line of code or somewhere else? I've just run this search isolation and it was fine (apart from SearchFormat as I'm on xl2k, what is that?)

Could you post some more of your code showing which line is highlighted for the error?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Molby]

Hi Loomah,

The only code I have is:

Range("a1&quot.Select
Cells.Find(What:="*01/2004", After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate

The error occurs on the whole of the Cells.find etc.

I think it has something to do with the '/'. When I do a search for "*01/" only it seems to work, but when I'm doing "*01/2004" it doesn't, I'm most confused!!

Sorry don't worry about the search format I'm on 2002, obviously something extra they've added.

Thanks,

Ian

 

[Loomah]

Ian
I've just had another look at this and I can generate the error if the date format on the sheet is not exactly the same as what you are looking for - ie the format must be dd/mm/yyyy.

I know you've already said they are in that format but it may be worth checking.

Also, try searching for a specific date. I'd suggest something that can't be confused with mm/dd/yyyy format such as 29/01/2004 to see if it finds it ok. Where I'm going with this is checking your regional settings for date format.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Molby]

Hi Loomah,

I think there is something weird going on with the formats here. I've already checked my regional settings and they are set up as UK (can't tell by looking at the Date as it's the 12/12 today typical, and I don't have access to change the date on the pc here at work), and so are the settings on the spreadsheet, but I have been playing around and found that "1/*/2004 finds the first occurence that I wanted, so it must be something to do with the date format being MM/DD/YYYY??? VBA doesn't have it's own settings does it?

Thanks,

Ian

 

[TonyJollans]

Hi Molby,

The reason you get the error 91 is because the Find has failed and there isn't a result cell to Activate. I don't know anything about the SearchFormat but I suspect it is behind the fact that the Find fails.

Enjoy,
Tony

 

[Molby]

Hi Loomah/Tony.

I resolved this by using:
Selection.NumberFormat = "dd/mm/yyyy"
Even though I had previously set the column to be in the DD/MM/YYYY format, sigh why doesn't excel remember the correct format. >:-<

Have a star Loomah as you put me in the right direction.

Thanks,

Ian