find date macro not working

still learning

Well-known Member
Joined
Jan 15, 2010
Messages
622
Office Version
  1. 365
Platform
  1. Windows
Hi
I've been using the following macro as part of a bigger one for a few years.
Excel Formula:
Sub gotodateday()
    Dim C As Range
    Set C = Range("B1:O1300").Find(Date)
    If Not C Is Nothing Then C.Select
End Sub
before 1/23/2021, it work fine
On 1/23/2021, didn't work. nothing happened when the macro was supposed to find the date. I didn't have time to mess with it, so I worked around it.
On the next day it worked fine and up until today.
This morning I went into two workbooks and the macro didn't finde today's date.
The first think I did was check the Desktop's Date. It is correct.....1/28/2021
I then changed the format of the date to different ones. It didn't help
I changed where today's' date is on the sheets to ....=today()..that didn't help
I opened a new workbook and worksheet. Ityped in t todays date in a cell in the range of the macro and copied the macro to it's new module. Didn't work
I tried a bunch of different formats and couldn't get it to work....again i tried ...=today()

any ideas???

mike
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
382
Office Version
  1. 2007
Platform
  1. Windows
Hello Still Learning,
if you still not resolved this problem,
try to format all your date ranges automatically before you doing search.
Something like this...

VBA Code:
Dim C As Range
Dim vR As Range

Sub DateFormating()

    For Each vR In [B1:O1300]
        vR = Format(CStr(vR), "mm/dd/yyyy")
    Next

End Sub

Now search for a specific format of date.

VBA Code:
Sub gotodateday()
    
    Set C = [B1:O1300].Find(Format(Date, "mm/dd/yyyy"))
    If Not C Is Nothing Then C.Select
    
End Sub
 
Solution

still learning

Well-known Member
Joined
Jan 15, 2010
Messages
622
Office Version
  1. 365
Platform
  1. Windows
Hi EXCEL MAX
Thank you
What I did was change the format in your gotodateday code to match what is in each sheet. Each sheet has only one date format for the whole sheet
It worked
Wonder why it started to only work on some days?????
I'll keep it, but I'm going to try mine tomorrow and see what happens.

Mike
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
382
Office Version
  1. 2007
Platform
  1. Windows
I can't surely say why it's hapenning on some days, depends of flows of actions, but common reason may spelling or formating.
If won't work for some another day try this...
Check you are spelling date correctly, for example sometimes number "1" may look as "l".
If this is OK and you inserting date with code, then format date with code before inserting to the sheet.
I'm glad if this was helpful.
 

still learning

Well-known Member
Joined
Jan 15, 2010
Messages
622
Office Version
  1. 365
Platform
  1. Windows
I didn't have time yesterday to even turn on the computer,but today, I changed back to my original find date code and it worked in both workbooks.
I now have a way to use my macro if part of the code doesn't work.
here is one of the original full codes. the other is too long to put here
VBA Code:
Sub finddate()
     '  new opening of sheet
    On Error GoTo errorline
     Cells.Find(Date).Select
    'Set C = [B1:O1300].Find(Format(Date, "mm/dd/yy"))
    If Not C Is Nothing Then C.Select
    findlastrow
    Exit Sub
errorline: newdate
End Sub
On Thursday when the code wasn't working, I added your code and put the ' in front of mine. Today I put it back
VBA Code:
Sub findlastrow() 
x = Range("B" & Rows.Count).End(xlUp).Row
Range("B" & x + 1).Select
ActiveWindow.ScrollRow = ActiveCell.Row - 15
End Sub
VBA Code:
Sub newdate()
  ' to bring up msgbox and put in new date
findlastrow
     YesNo = MsgBox("     New Date? ", vbYesNo)
 Select Case YesNo
Case vbYes
putindate
 End Select
End Sub
Putindate formats the cell in A that is the last row plus 1 to....mm/dd/yy

Nothing changes in A except when the macro puts in the date.
It's is odd, but I since I can use your code, I'm set
Thank you again for your help

On opening the sheet, the cursor looks for todays date. If it's found, it moves to the last line in B. If it's not found, it still moves to the last line in B
If today's date is not found, A message box comes up and asks if I want a new date (incase I wanted to add something to yesterday)
It then puts in todays date in A, and moves back to B.

mike
 

Watch MrExcel Video

Forum statistics

Threads
1,127,328
Messages
5,624,034
Members
416,007
Latest member
csf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top