#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    5
    Rep Power
    0

    Macro To Convert Excel Formulas To Values


    How would I code a VBA macro for an excel spreadsheet that would convert all formulas on the sheet to values???? I would just need a one click button.
  2. #2
  3. Type Cast Exception
    Devshed Supreme Being (6500+ posts)

    Join Date
    Apr 2004
    Location
    OAKLAND CA | Adam's Point (Fairyland)
    Posts
    14,883
    Rep Power
    889
    There's several ways, but .Value = .Value works for one cell

    Code:
    Sub DemoThis()
      ' // Make a formula
        ActiveSheet.Cells(1, 1).Formula = "=4^.2*3-.3"
      ' // Change the cell to the actual value of the formula
        ActiveSheet.Cells(1, 1).Value = ActiveSheet.Cells(1, 1).Value
    End Sub
    Where this does the whole sheet

    Code:
    Sub ConvertAllCellsToValues()
      ' // Copy everything
        ActiveSheet.Cells.Copy
      ' // Paste special with values
        ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
    End Sub

    Comments on this post

    • ingenuity agrees
    • siavashhn agrees : Good job.
    medialint.com

    “Today you are You, that is truer than true. There is no one alive who is Youer than You.” - Dr. Seuss
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2006
    Posts
    5
    Rep Power
    0
    Thanks medialint, you rock =)
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2015
    Posts
    2
    Rep Power
    0

    Thumbs up Thanks


    Originally Posted by medialint
    There's several ways, but .Value = .Value works for one cell

    Code:
    Sub DemoThis()
      ' // Make a formula
        ActiveSheet.Cells(1, 1).Formula = "=4^.2*3-.3"
      ' // Change the cell to the actual value of the formula
        ActiveSheet.Cells(1, 1).Value = ActiveSheet.Cells(1, 1).Value
    End Sub
    Where this does the whole sheet

    Code:
    Sub ConvertAllCellsToValues()
      ' // Copy everything
        ActiveSheet.Cells.Copy
      ' // Paste special with values
        ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
    End Sub
    Thank you very much medialint. your post, helped me on 2015 !!!
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2015
    Posts
    2
    Rep Power
    0
    I used :

    Range("M2").Select
    ActiveCell.Value = ActiveCell.Value

IMN logo majestic logo threadwatch logo seochat tools logo