Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formulas -> Values (2000 sp-3/ 98SE)

    I have been using the following simple macro code to convert formulas to values (for a selection of just a single, contiguous area):

    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False

    I want to extend this for a multi-area selection (not hard I think, using the Areas collection), but my question is whether there's a better (more fundamental?) way to convert cell contents from formulas to their resultant values.

    thanks

  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: Formulas -> Values (2000 sp-3/ 98SE)

    If you don't have a contiguous selection, your macro will put values in more than just the selection, since you may select the used range.

    For example if only 2 noncontiguous cells are chosen, the rows.count*columns.count = 1 and will force the entire used range of the cell to be selected, which may not be what is desired.

    You might also want to check for multiple areas in an ElseIf to workd through the cells in each of the areas

    A variant of the code from <post#=394839>post 394839</post#> could do it (just getting rid of the test for a link.

    Steve

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas -> Values (2000 sp-3/ 98SE)

    Thanks Macropod. I guess it's "suck it and see" wrt efficiency. I generally try to avoid using Selection in macros, but that may not be the most appropriate philosophy here. I presume you meant to include a PasteSpecialValues macro here, with PasteSpecial Paste:=xlValues.

    Alan

  4. #4
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas -> Values (2000 sp-3/ 98SE)

    Steve

    Just trying to get this right. Is what you say a result of the .Columns or .Rows property looking at just the first contiguous area of the selection range?

    Alan

  5. #5
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas -> Values (2000 sp-3/ 98SE)

    Alan,

    Here's the macro I've been using. Seems to work OK on discontiguous selections

    Sub PasteValues()
    Dim rngFormulas As Range
    Dim rngCell As Range
    Set rngFormulas = Selection.SpecialCells(xlCellTypeFormulas)
    For Each rngCell In rngFormulas
    rngCell.Formula = rngCell.Value
    Next
    Set rngFormulas = Nothing
    End Sub

    Ken

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas -> Values (2000 sp-3/ 98SE)

    That looks good - it avoids the copy/ paste and specifically targets cells containing formulas. I think this is the one I'm after. Thanks Ken.

    Alan

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formulas -> Values (2000 sp-3/ 98SE)

    Hi Steve,

    Apologies for any confusion - my macro was only intended as a demo of looping through a single range using cell.value = cell.value instead of selecting the range and using pastespecial.values. I wasn't intending it to meet all of Alan's requirements.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  8. #8
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formulas -> Values (2000 sp-3/ 98SE)

    Hi Alan,

    To convert an existing range, you could use:
    cell.value = cell.value

    For example:

    Sub Form2Val()
    On Error Resume Next
    Dim Cell As Range
    Dim CellCount As Long
    If Selection.Rows.Count * Selection.Columns.Count > 1 Then
    CellCount = Selection.Rows.Count * Selection.Columns.Count
    Else
    CellCount = ActiveSheet.UsedRange.Rows.Count * ActiveSheet.UsedRange.Columns.Count
    End If
    For Each Cell In Selection.SpecialCells(xlCellTypeFormulas)
    Cell.Value = Cell.Value
    Next Cell
    MsgBox "Finished changing " & CellCount & " cells.", 64
    End Sub

    My own paste values macro is somewhat shorter than yours:

    Sub PasteSpecialValues()
    On Error Resume Next
    Selection.PasteSpecial Paste:=xlValues
    End Sub

    Whether the paste special approach is quicker than looping through ranges and using cell.value = cell.value is something that could be timed.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  9. #9
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: Formulas -> Values (2000 sp-3/ 98SE)

    Hi Alan,

    Yes, I posted the wrong pastespecial macro. Updated now.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas -> Values (2000 sp-3/ 98SE)

    Thanks Macropod (and to Steve & Ken too). Clearly, as with many things VBA, there's more than one method available. I had several thoughts initially, plus some new ones now, to add to the choices. I think they'll all work if implemented appropriately, but I'd like to discover which is the best wrt efficiency and good coding practice.

    I'm pondering: "Would it me more efficient to use XL's inbuilt worksheet functions or to loop through cells in a range?" The Copy/ Paste Special operation (your amended method, or my initial one) would require XL to (internally) evaluate all cells in the selected range. But this might end up being more efficient than looping through the same range via VBA, but evaluating only those cells containing formulas. I'd also tried to avoid copy/paste operations in VBA macros, but maybe this "wisdom" is misplaced in this context? I suppose the answer's only going to come through testing. And it mightn't matter a flea's whisker anyway. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Alan

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Formulas -> Values (2000 sp-3/ 98SE)

    How about something simple like :

    Range = Range.Value,

    where Range is the range to convert.

    Andrew C

  12. #12
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas -> Values (2000 sp-3/ 98SE)

    Some interesting results trying to implement this one Andrew. In the attached sheet I have 3 separate ranges, replicated from Row 20 for testing purposes. The macro below seems to do its job on a single, contiguous range, but goes haywire if multiple areas are selected. I can't figure out why off hand, but will do some debugging.
    <pre>Public Sub convRange()

    Dim rngFormulas As Range
    Set rngFormulas = Selection.SpecialCells(xlCellTypeFormulas)
    rngFormulas = rngFormulas.Value
    Set rngFormulas = Nothing

    End Sub
    </pre>

    Alan

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

    Re: Formulas -> Values (2000 sp-3/ 98SE)

    See my contemporaneous reply to Andrew, which was already addressed by Steve eariler in the thread.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Formulas -> Values (2000 sp-3/ 98SE)

    If range is not contiguous, such as a multi area range name or multiple area selection:

    Dim rng As Range
    For Each rng In Selection.Areas
    rng.Value = rng.Value
    Next rng

    otherwise Areas after the first are set to the value in Areas(1).Cells(1,1). (Another thing I learned the hard way.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formulas -> Values (2000 sp-3/ 98SE)

    Yes, I see the patern. I'm getting replications from Areas(1) in a rather complex fashion - not just from Cells(1,1), but from subsequent cells in that area too. I will take a harder look at what's actually happening here and try to figure it out. Thanks.

    Alan

Page 1 of 2 12 LastLast

Posting Permissions

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