Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts

    Values Instead of Formulas (2002 SP-2)

    I can copy a formula and use paste special/values to show the numbers only.

    Is there a way to do this with a function or formula that can be typed? How about VBA?

  2. #2
    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: Values Instead of Formulas (2002 SP-2)

    Not sure I understand what you want:
    If you use a formula it is still a formula not a value.
    If you want a VB macro, why not just use copy-paste special values?

    If this is a routine you can use copy -paste values directly in VB or use other coding. Could you elaborate on what you are trying to do?

    Steve

  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: Values Instead of Formulas (2002 SP-2)

    If you mean Paste Special | Values, there are a couple of non-macro ways:

    1. Add the paste Values icon to your Toolbar and use it
    2. RightClick the target range and select Paste Special | Values
    (and there may be more)

    The VBA code to Paste Value(s) -after- a range has been copied would run something like:

    Sub PasteValues()
    If Application.CutCopyMode = xlCopy Then Selection.PasteSpecial Paste:=xlValues
    Application.CutCopyMode = False
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts

    Re: Values Instead of Formulas (2002 SP-2)

    I'm envisioning something like this:

    1) I have a formula in column A, toggled so values are showing.
    2) I would like to be able to go to B1 and type a function, called (say) valueCopy(A1) that will copy the value shown for the formula in A1 only over to B1.

    Does such a thing exist?

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

    Re: Values Instead of Formulas (2002 SP-2)

    Why do you need to do this, it will just repeat the returned value in the source cell? If you have a formula in A1 which returns a value, and you want to repeat the value in cell B1, why not just use '=A1' in cell B1?
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    4 Star Lounger
    Join Date
    Jun 2003
    Location
    Utah, USA
    Posts
    405
    Thanks
    35
    Thanked 5 Times in 5 Posts

    Re: Values Instead of Formulas (2002 SP-2)

    Because I'd like to retain the current value of A1 in B1 (say, as a basecase), while continuing to allow column A to be updated as the underlying data change.

    As I remarked in my first post, I can do this with paste special/values. The disadvantage of this is that while it will place the value in B1, it will not leave any notation there about where that number came from. If there is a function that will do this, and show that B1 was at some point derived from A1, it would be relatively obvious that the value retained in B1 was a base case from some earlier run.

Posting Permissions

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