Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Crestview Hills, Kentucky, USA
    Posts
    84
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formula Help (Excel 2000)

    I have a command button that runs a macro that places today's month and year in a cell. I used the today() function.

    The trouble is, the actual value of the target cell is "today()" so if the saved document is opened next month it will show February-03. How can I make the target cell actually contain "Jan-03" instead of "Value()?"

    Thanks,
    Craig.

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Formula Help (Excel 2000)

    You can convert a formula to its' result by copying the cell and using Edit, Paste Special, Values.

    The VBA code to do this would look something like:
    <pre> Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    </pre>


    If you need a more explict answer - just ask, someone will be happy to chime in!

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Formula Help (Excel 2000)

    Instead of pasting the =NOW() formula to the cell, adjust your code to

    With Range(myDateCell)
    .Value = Date ' edited
    .NumberFormat = "mm/dd/yy" ' or whatever floats the boat
    End With
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Formula Help (Excel 2000)

    I knew you'd have the answer!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formula Help (Excel 2000)

    You could also press control+; (semicolon) to enter the current date into any cell. Get the time with control+: (colon)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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