Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Sep 2001
    Location
    Philadelphia suburbs
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    showing both formula and value (Excel 2000)

    A User wants to automate the rounding capability so that he just goes to certain cells and runs a macro. However, when he is in a cell, he wants to be able to see the rounded value in the cell, as well as the formula with the original, decimal number (As he can if he just enters the round function in the cell). Is this possible? I could write a macro to round the value using a variable, but I couldn't figure out a way to also show the original number.

    Thanks very much .

    Sue

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: showing both formula and value (Excel 2000)

    How many places do you want to round to? If you want to round to an integer, then does this do what you want?

    <pre>Public Sub RoundCell()
    If (ActiveCell.Value = "") Or Not IsNumeric(ActiveCell.Value) Then Exit Sub
    If ActiveCell.HasFormula Then
    ActiveCell.Formula = "=ROUND(" & Right(ActiveCell.Formula, Len(ActiveCell.Formula) - 1) & ",0)"
    Else
    ActiveCell.Value = "=ROUND(" & ActiveCell.Value & ",0)"
    End If
    End Sub
    </pre>

    Legare Coleman

  3. #3
    Lounger
    Join Date
    Sep 2001
    Location
    Philadelphia suburbs
    Posts
    29
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: showing both formula and value (Excel 2000)

    Legare,

    Your solution works perfectly.

    Thanks so much.

    Sue

Posting Permissions

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