Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Posts
    85
    Thanks
    0
    Thanked 0 Times in 0 Posts

    code to convert forumla to number. (97)

    ActiveCell.FormulaR1C1 = "=TODAY()+7-WEEKDAY(TODAY())"
    ActiveSheet.Range("A1").Value = Date

    The above code inserts Saturday's date in the active cell. How can I get this date to then become static, rather than remain a formula?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: code to convert forumla to number. (97)

    You can set the value of the cell to the result directly by using the VBA equivalent of the formula:

    ActiveCell = Date + 7 - Weekday(Date)

    or, if you prefer to use the formula, you can "freeze" the result by setting the value of the cell to itself:

    ActiveCell.FormulaR1C1 = "=TODAY()+7-WEEKDAY(TODAY())"
    ActiveCell = ActiveCell

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: code to convert forumla to number. (97)

    You never have to make it a formula, use:

    ActiveCell.value = date+7-WEEKDAY(date)

    will do it.

    The 2nd puts today's date in it. It is already static.

    Steve

Posting Permissions

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