Excel VBA: Convert Formula to Value Automatically (2 Easy Methods)

Excel VBA: Convert Formula to Value Automatically (Quick View)

Sub Convert_Formula_to_Value_1()

Sheet_Name = "Sheet1"

Set Cell_Range = Worksheets(Sheet_Name).UsedRange

For i = 1 To Cell_Range.Rows.Count
    For j = 1 To Cell_Range.Columns.Count
        Cell_Range.Cells(i, j) = Cell_Range.Cells(i, j).Value
    Next j
Next i

End Sub

Convert Formula to Value Automatically in Excel VBA: 2 Suitable Methods

We’ve got a worksheet called Sheet1 that contains the names of some candidates, their marks in written and viva of a recruitment test, the average marks, and a recommendation mentioning whether they are selected. The average marks and the recommendation columns contain formulas.

Method 1 – Convert Formula to Values Automatically by Iteration in Excel VBA

• Use this code:

⧭ VBA Code:

Sub Convert_Formula_to_Value_1()

Sheet_Name = "Sheet1"

Set Cell_Range = Worksheets(Sheet_Name).UsedRange

For i = 1 To Cell_Range.Rows.Count
    For j = 1 To Cell_Range.Columns.Count
        Rng.Cells(i, j) = Cell_Range.Cells(i, j).Value
    Next j
Next i

End Sub

⧭ Output:

• Run this code (Change the name of the worksheet according to your need). It’ll convert all the formulas of the worksheet to their values.

Read More: Convert Formula to Value in Multiple Cells in Excel

Method 2 – Convert Formula to Values Automatically by Copying and Pasting in Excel VBA

• The complete VBA code will be:

⧭ VBA Code:

Sub Convert_Formula_to_Value_2()
Sheet_Name = "Sheet1"
Set Rng = Worksheets(Sheet_Name).UsedRange
First_Cell = Rng.Cells(1, 1).Address
Rng.Copy
Worksheets(Sheet_Name).Range(First_Cell).PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub

⧭ Output:

• Run this code (Change the worksheet name in the code to match yours). It’ll convert all the formulas of the worksheet to their values.

Developing a Macro to Convert Formula to Values Automatically Using VBA Code

⧪ Step 1 – Opening the VBA Window

• Press Alt + F11 on your keyboard to open the Visual Basic window.

⧪ Step 2 – Inserting a New Module

• Go to Insert and choose Module in the toolbar. A new module called Module 1 (unless you have other modules) will open.

⧪ Step 3 – Putting the VBA Code

• Insert any of the two given VBA codes in the module.

⧪ Step 4 – Running the Code

• Click on the Run Sub / UserForm tool from the toolbar.

• The code will run. All the formulas of your input worksheet will be converted to their values automatically.

Download the Practice Workbook

Related Articles

• How to Return Value of Cell Not Formula in Excel
• How to Stop Formula to Convert into Value Automatically in Excel
  
• How to Convert Formula Result to Text String in Excel
• Putting Result of a Formula in Another Cell in Excel

<< Go Back to Convert Formula to Value in Excel | Excel Formulas | Learn Excel