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]

try the two below, see which one works better for you:

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

 

[still learning]

Hi Bobsan42
Thanks for the response.
I tired both taking out the original. (2 separate codes)
I tried when my micro worked and when it didn't.
Neither worked.
I first thought i had gotten some kind of virus. I ran my Norton Antivirus program on all of excel and also on the files i'm having problems with and the testing file.

I forgot to mention that I have calendar sheets in two separate workbooks and both act the same. That's why I thought it was the code.

I also forgot to include that I open both with buttons on my menu file. one button opens the workbook and then opens sheet 1. there are 3 other sheets with macros that work fine. (but none that look for today)

Sub bills()
Workbooks.Open Filename:="C:\excel\Bills 2025.xlsm"
Sheets("2025").Select
End Sub

the other button doesn't have to because i only have one sheet in the workbook.

Sub upcoming1()
Workbooks.Open Filename:="C:\excel\upcoming v6.xlsm"
End Sub

mike

 

[Alex Blakenburg]

In your search range B1:O1300 are the dates formulas or values ?
If formulas you will need to use xlValues and then it becomes very sensitive to the formatting ie the displayed format and the search formatting has to match. In that case you could try something like this

    With Range("B1:O1300")
        ' Assume row 1 is a a heading row - use cell format from row 2
        Set c = .Find(Format(Date, .Cells(2, 1).NumberFormat), , xlValues) 
    End With

 

[still learning]

Hi Alex
The dates are entered manually, then formatted to "D".
But the macros sometimes work perfect and sometimes not.
I'm wondering if it's a computer problem.
the macro doesn't recognize today"s date., so it does nothing
I've tried to use the macro in a new sheet in a new workbook and I get the same results.

I'm trying to have the cursor go to today"s date on the calendar. It does sometimes. I've tried to duplicate what i did when it doesn't work,
but I can't. I've had it work good, leave it open and open another workbook, do something in it, go back to the calendar and save and close it.
when i open it, sometimes it work and sometimes it doesn't.

I have 2 calendars in separate workbooks that do the same thing using this macro.
I deleted both files and started new and it didn't help.

I made a new sheet and put in today's date using the default format. I tried using the macro and AGAIN sometimes it works and sometimes not.

I also tried opening the sheet with out using ...worksheet-open. it opens where the cursor was when it was closed. I then put an icon on the ribbon to use the "gotodateday" macro. same results sometimes good and sometimes bad.

I wonder if I can re-write the macro completely using a different approach.

The other macros in the workbook/sheets work ok, BUT they are not looking for a date.
All was good till about the end of last year.

mike

 

[bobsan42]

just to confirm what you are trying to explain, try this code (add it after set c = .... ):

if not c is nothing then 
 c.select
 msgbox c.address
else
 msgbox "date not found"
end if

 

[still learning]

Hi bob
Thank you for your response
Your response game after I responded to Alex

Sub gotodateday3()
    Dim C As Range
    'Set C = [B1:O1300].Find(Format(Date, "d"))
   Set C = Range("B1:O1300").Find(Date)
   If Not C Is Nothing Then
 C.Select
 MsgBox C.Address
Else
 MsgBox "date not found"
End If
End Sub

When my code is working, I get a message box showing the cell address.
When the macro is not working, I get a message showing "date not found"

I now have the macro in 3 sheets, 2 calendars, (original place), and a new sheet that only has today's date on it .formatted "D".
I tried it with the default format, too. mm/dd/yyyy
either all 3 work or don't work.
All was good till about the end of last year.

I can download the new sheet that only has the date on it to see if it is my computer.
PC (NOt MAC)
Windows 10
Office 365
64 bit


mike

 

[Joe4]

A shot in the dark, but are you sure that there isn't a hidden time component to your dates?
If you temporarily change the formats of those date fields "General", you should see it show large Integers.
Do any of those integers have a fractional component after them?

 

[bobsan42]

if you type the date manually as Date datatype and the cell value is only formatted differently this should work:

   Set C = Range("B1:O1300").Find(Date, , xlFormulas)

the problem is in the date formatting as far as I tried. probably somewhere along the way your machine date/time settings have been changed.
in the VBE immediate window type ?Date to see the actual date format you are looking for
when you use format(date,"d") - this doesn't work because you are looking for text to match a number

 

[still learning]

Hi Joe

I typed in every date for the month,(3/1/2025) and then went back and high lighted all the dates formatted it >>custom "D"
I have also tried with the dates not formatted
the only thing in the cell is the date. it was blank before i started

Hey Bob,

After I read your response, I went to my windows setting and look at the clock. doesn't look like nothing is wrong.

I added your code to mine. it seems to be working.
I put it in my new sheet and it is working when the calendars' code is not.
I'm going to have to shut down for a bit. when I come back I'll try it again

Where is the VBE immediate window?

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

Thank you all for the help
I'll click the check mark when i come back and let you all know it works.

mike