find a date macro quits working

[still learning]

Hi
Sorry that's it a long question but I wanted to explain what I tried.
I made a calendar years ago and have the dates formatted as “D”
I use the following macro when I open the workbook (I added enableEvents thinking it would help. And left it in.)

Private Sub Workbook_open()
Application.enableevents = True
gotodateday
End Sub

I also have it in the sheet when I go to a different sheet and come back

Private Sub Worksheet_activate()
Application.enableevents = True
gotodateday
End Sub

This is the macro I’m trying to run

Sub gotodateday()
    Dim C As Range
   Set C = Range("B1:O1300").Find(Date)
    If Not C Is Nothing Then C.Select
End Sub

What happens is when I first open excel and go the workbook, I works fine.
When I’m done added events to the calendar, I save and close the workbook.
If I go back to it after a few minutes, the sheet opens but the macro doesn’t do any thing
I changed the macro at the end to open a message box to see it will work. But i get the same results. I got an error message that I need “End If” and added that, to the revised macro.

Sub gotodateday()
    Dim C As Range
   Set C = Range("B1:O1300").Find(Date)
    'If Not C Is Nothing Then C.Select
    If Not C Is Nothing Then
   msgbox ("no date")
   End If
End Sub

I then put it in a new workbook/sheet with a cell that has today’s date and it works good, I save and closed the workbook and go back to it a few minutes later, and it does nothing.
To make sure my syntax is correct, I put this macro in the new sheet and works fine.
I save and close and it. After a few minutes, I open it and run the macro and it works.

Sub test()
msgbox ("no date")
End Sub

I ranged name the cell with today’s date and made a macro to go to that range name and it works when the other macro doesn’t. ( in the new sheet)

If I close excel completely and start it again, these macros work fine. Then after a few minutes it doesn’t
I have no problem with any other macros in the same Calander workbook/sheets
Its been working fine for a couple of years till about January of this year.


mike

 

[bobsan42]

Where is the VBE immediate window?

Alt+F11 (to open VBE)
Ctrl+G (or View / Immediate Window)
type ?Date then Press Enter

BTW: Just to show that Cell Value Format makes a difference in the context of .Find()
If you date cell is formatted as "d", then this will also probably work:

   Set C = Range("B1:O1300").Find(Day(Date), , xlValues, xlWhole)

But will not work correctly if you have many months on the same sheet

 

[still learning]

Hi All
the VBE immediate window showed the date as mm/dd/yyyy
I used your fix, Bob and haven't had a problem all day.
weird that it was fine for years and then started to mess up.
At least it's working now

Thanks again All for your help

I'm going to post another question unrelated to this

mike

 

[Alex Blakenburg]

it was fine for years and then started to mess up.

In your original Find line you were not specifying any of its parameters. A number of those parameters persist ie once they are set they continue to use that setting until you change it or until you close and restart Excel.
The setting that is critical in finding dates is LookIn:=xlFormulas. If xlFormulas is far more forgiving than xlValues. xlFormulas looks for date values while xlValues looks at formatted value as if it were Text. If you have a choice ie the dates are not a result of formulas then use xlFormulas.

Your issue indicates that either programmatically or manually you have changed the setting to xlValues. I suspect that your issues started when you implemented the A-Z Sort code in this thread, which sets it to xlValues.

Sort A to Z with changing last row

Hi I’m trying to sort from data in A to L The last row changes with new data I have A3 frozen as I use it as a header Data starts at A4 A:A has dates in it I need the oldest date to start in A4 (A to Z) As I add data, sometimes I can’t add in order This works great but it doesn’t go to the last...

www.mrexcel.com

It is safer to always set the parameters that persist especially, LookIn:=xlFormula or xlValues, LookAt:=xlWhole or xlPart