+ Reply to Thread
Results 1 to 7 of 7

Jump to today's date

Hybrid View

  1. #1
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Jump to today's date

    Hello everyone
    I have dates in column A
    I tried to devise code that enables me to jump to the cell that holds today's date using this code
    Sub FindDate()
        Dim C As Range
        Dim Str As String
        Str = Format(Date, "dddd yyyy/mm/dd")
        Set C = ActiveSheet.Cells.Find(What:=Str, LookIn:=xlValues, SearchFormat:=True)
        If Not C Is Nothing Then C.Select
    End Sub
    But it doesn't work for a reason which I ignore
    < ----- Please click the little star * next to add reputation if my post helps you
    Visit Forum : From Here

  2. #2
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Jump to today's date

    Thanks I could figure it out
    Sub FindDate()
        Dim C As Range, StrDate As Date
        StrDate = CLng(Date)
        
        Set C = ActiveSheet.Cells.Find(What:=StrDate, LookIn:=xlFormulas)
        If Not C Is Nothing Then C.Select
    End Sub

  3. #3
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Jump to today's date

    Hi Yasser,

    Finding Dates can be difficult, as you have found. The trick is to format the string so that Excel understands what you are looking for. In the US it is "m/d/yyyy". It is possible that all over the world, that VBA uses the US format. Do not pay attention to the date format in the cell. What is important is the date itself.

    See http://www.ozgrid.com/VBA/find-dates.htm and
    http://www.cpearson.com/Excel/DateTimeVBA.htm

    The following small modification of your code worked for me.
    Sub FindDate()
        Dim C As Range
        Dim Str As String
        Str = Format(Date, "m/d/yyyy")
        Set C = ActiveSheet.Cells.Find(What:=Str, LookIn:=xlValues, SearchFormat:=True)
        If Not C Is Nothing Then C.Select
    End Sub
    You may have to adjust the format string slightly, because you are not in the US. If the code does not work as is, my first try would be "d/m/yyyy".

    Lewis

  4. #4
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Jump to today's date

    Thanks a lot Mr. Lewis for sharing this issue
    In fact I tried both suggested formats but it doesn't work
    Column A is formatted in Custom format "dddd yyyy/mm/dd "

    It works with your format in your code "m/d/yyyy" just after changing this line
    Set C = ActiveSheet.Cells.Find(What:=Str, LookIn:=xlFormulas)

  5. #5
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Jump to today's date

    Column A is formatted in Custom format "dddd yyyy/mm/dd "
    Each Excel cell really has two values:
    a. The value stored in the internal manner (dates are stored as type double).
    b. The PrettyPrint value you see after the cell is formatted.

    When doing a find, we need the 'internal' storage. Your PrettyPrint "dddd yyyy/mm/dd " format is irrelevant.

  6. #6
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Jump to today's date

    As Lewis has said the format of the cell(s) is irrelevant. If the dates are true date numbers (you can test with =ISNUMBER(A1) ) then you need to find the date number. If the 'dates' are in fact text strings then clearly you need to convert today's date to a string that's in the same format.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  7. #7
    Forum Expert
    Join Date
    04-23-2009
    Location
    Matrouh, Egypt
    MS-Off Ver
    Excel 2013
    Posts
    6,892

    Re: Jump to today's date

    Thank you very much for this useful information
    Thanks a lot for all great help
    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 10
    Last Post: 09-06-2023, 08:19 PM
  2. [SOLVED] Formula to compare date (including month and year) from a listed date to today's date
    By mhewitson15 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-11-2014, 05:31 PM
  3. [SOLVED] Hyperlink to jump to today's date
    By marcimarc in forum Excel General
    Replies: 3
    Last Post: 12-19-2013, 10:31 AM
  4. condit. format: If date in cell = today, display "Today"
    By ratkins in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-14-2013, 01:33 PM
  5. Replies: 6
    Last Post: 08-08-2011, 08:32 AM
  6. Excel 2008 : Jump to today's Column
    By danielwalters6 in forum Excel General
    Replies: 5
    Last Post: 04-15-2010, 01:12 PM
  7. Macro to jump to today's date
    By Victor Delta in forum Excel General
    Replies: 9
    Last Post: 07-04-2006, 04:00 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1