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

    Converting Formulas to Values (Excel 2000)

    Are there any other methods for converting formulas to values other than using paste special?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

    Re: Converting Formulas to Values (Excel 2000)

    The alternative would be a macro that loops through the cells in the selection and sets their formula to their value. Paste Special, however, is much more efficient for large ranges. I have a little macro in my Personal.xls:

    Sub Formulas2Values()
    On Error GoTo ExitHere
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    ExitHere:
    End Sub

    I have assigned this macro to a custom toolbar button for easy access. (You can also assign a keyboard shortcut if you like)

  3. #3
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Converting Formulas to Values (Excel 2000)

    Catharine / Hans,

    The Edit menu has a Paste Values menu item available. (View | Toolbars | Customize)

    It can also be added to the Cell right-click menu and Row & Column pop-up menus with something like this...
    '---------------------------------------------------
    'ID 370 is the Paste Values menu item

    Dim NewItem As CommandBarButton
    Set NewItem = CommandBars("Cell").Controls.Add(ID:=370, before:=4)'_CELL SHORTCUT
    NewItem.Style = msoButtonCaption
    NewItem.Copy Bar:=CommandBars("Row"), before:=4 '_ROW SHORTCUT
    NewItem.Copy Bar:=CommandBars("Column"), before:=4 '_COLUMN SHORTCUT
    Set NewItem = Nothing
    '-----------------------------------------------------------------------------------

    Also, a little less complicated way to use code to convert cells to values would be...

    Selection.Value = Selection.Value

    Regards,

    Jim Cone
    San Francisco, CA

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

    Re: Converting Formulas to Values (Excel 2000)

    Jim,

    Thanks for the tips!

  5. #5
    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: Converting Formulas to Values (Excel 2000)

    The alternative would be a macro that loops through the cells in the selection and sets their formula to their value
    -------------------------------------
    How would you do that? I don't find anything other than paste special using the object browser.

    I'm playing around with converting formulas to values as I'm dealing with a huge spreadsheet, and the number of cells with formulas is crashing Excel...
    (I do remember the advice about Statistica.)
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    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: Converting Formulas to Values (Excel 2000)

    This should work. It takes the selections, finds all the cells with formulas and then loops thru those cells setting their value to their value eliminating the formula. (essentially doing a "pasteSpecial values")

    <pre>Option Explicit
    Sub ConvertFormulas()
    Dim rng As Range
    Dim rcell As Range

    Set rng = Selection.SpecialCells(xlCellTypeFormulas)

    For Each rcell In rng
    rcell.Value = rcell.Value
    Next rcell
    End Sub</pre>


    Steve

  7. #7
    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: Converting Formulas to Values (Excel 2000)

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

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts

    Re: Converting Formulas to Values (Excel 2000)

    Just thought I'd throw my thoughts into the pot:
    1. To convert a range to values:
    select range, right-click drag away and back, then chose 'copy here as values only'

    2. In VBA
    Selection.Name = "blockA" 'assign temporary name
    z = [blockA] 'read into array
    [blockA] = z 'write back values from array

    ..ok, then delete the temporary name "blockA"

    zeddy

  9. #9
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Converting Formulas to Values (Excel 2000)

    You can right-click "copy" and right click "paste values" using my Add-in (XL Extras).
    Using it you can also:

    add a Table of Contents
    add data to the Header and Footer
    unhide all hidden sheets with one command
    sort worksheets
    and a few other things.

    Attached is a zipped 3 page Word.doc with pictures of the menus.
    If someone feels it could help, then I will post the Add-in.

    Jim Cone
    San Francisco, CA

  10. #10
    Lounger
    Join Date
    Apr 2001
    Location
    USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Formulas to Values (Excel 2000)

    Jim,

    Please post your add-in. It is just the thing to solve a request from my brother. Please let me know if there is a charge for the add-in.

    Thanks,

    Dr. Andy

  11. #11
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Converting Formulas to Values (Excel 2000)

    Dr.

    I've attached my XL Extras Add-in, Release 1.10, for Microsoft Excel 97 or later versions. (zipped)
    There is no charge, however, I am curious as to what your brothers request is?

    This version has major modifications to the Insert Table of Contents feature.
    The Word.doc instructions posted above (a few days ago) should be read before trying to install the program.
    Also, these instructions do not highlight a 10th feature in the program - Flip Selection - on the Format menu.
    I would appreciate hearing any comments - pro or con - about the program.

    Regards,
    Jim Cone
    San Francisco, CA
    jim.coneXXX@rcn.comXXX

  12. #12
    Lounger
    Join Date
    Apr 2001
    Location
    USA
    Posts
    43
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting Formulas to Values (Excel 2000)

    Jim,

    My brother wanted the "Sort All Sheets" capability. He does clinical research and uses Excel for analysis and tracking of trial data. He has very large workbooks with many sheets (I'm aware of 2 with over 100 sheets each) and wants to be able to sort by sheet name after frequent renaming.

    Thank you fro your prompt response.

    Dr. Andy

Posting Permissions

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