Results 1 to 7 of 7
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Macro to insert date

    I have a simple macro attached to a command button, but it's not working right.

    When the button is pressed, I want to select a certain cell and plug today's date into it. Right now the code is

    Range("F10").Select
    ActiveCell.FormulaR1C1 = "Today()"
    Range("F11").Select

    What do I need to substitute for "Today()" to get it to put the date into the cell?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Lou,

    You can replace the 1st 2 lines with: [F11].formula = "=Now()"
    This is also more efficient...don't use SELECT unless you have to!

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    If you want the date without the time then either of these two will work

    HTH,
    Maud

    Code:
    Public Sub DateToday()
    'Places the formula in the cell
    ActiveCell.Formula = "=Today()"
    End Sub
    
    Public Sub DateToday()
    'Places the date directly in the cell
    ActiveCell = Date
    End Sub

  4. The Following User Says Thank You to Maudibe For This Useful Post:

    Lou Sander (2015-04-07)

  5. #4
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I didn't make it clear... I just want TODAY's date in the cell. It needs to stay there, unchanged, tomorrow, the next day, and forever, until I change it manually or click the command button again.

    It is telling me the day I checked my bank balance.

    The cell has a date format that doesn't show the time.

    This, based on Maudibe's suggestion, seems to work, though it goes against RetiredGeek's advice:

    Sub Macro3()
    '
    ' Macro3 Macro
    ' Macro recorded 4/7/2015 by Rankin Desktop
    '
    Range("F10").Select
    ActiveCell.FormulaR1C1 = Date
    Range("F11").Select
    End Sub
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  6. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Lou,

    [F11].value = Now() Just format the cell to the desired Date format.

    [F11].value = Date()

    HTH
    Last edited by RetiredGeek; 2015-04-07 at 20:18.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Lou Sander (2015-04-07)

  8. #6
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    Got it! I now have six macros for updating the dates in six different cells, and it works exactly as I want.

    Two questions:

    1) If I give a cell a name, how do I use the name in place of the [F11] or whatever?

    2) I might want to make a command button that updates all these dates at once. I'm guessing that I can just make its macro include all six of the [Fnn].value = Date() statements, each on a separate line. Am I right? (I guess I could just try it, but maybe an answer from an expert would be better, especially if I'm wrong.) :-)
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  9. #7
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,633
    Thanks
    115
    Thanked 647 Times in 590 Posts
    Named ranges
    [hello] = Date or Range("hello") = Date

    Multiple cells
    Range("H1,G2,D3") = Date
    Range("hello,G2,D3") = Date
    [hello,G2,D3] = Date

    HTH,
    Maud
    Last edited by Maudibe; 2015-04-07 at 23:17.

  10. The Following User Says Thank You to Maudibe For This Useful Post:

    Lou Sander (2015-04-09)

Posting Permissions

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