Convert Formulas into Values Using Macros In Excel

What does it do?

Converts formulas into values in your selection

Copy Source Code:

</p>
<p>Sub ConvertFormulasIntoValues()</p>
<p>Dim rng As Range<br />
Dim formulaCell As Range<br />
Set rng = Selection</p>
<p>'Check each cell in the range if it has a formula<br />
For Each formulaCell In rng<br />
If formulaCell.HasFormula Then<br />
formulaCell.Formula = formulaCell.Value<br />
End If<br />
Next formulaCell</p>
<p>End Sub</p>
<p>

Final Result: 

Exercise Workbook:

Download excel workbookConvert-Formulas-into-Values.xlsm

Here is our list of formula cells:

To have a better look, you can see it uses the RANDBETWEEN formula. Let us change these to values only!

STEP 1: Go to Developer > Code > Visual Basic

STEP 2: Paste in your code and Select Save. Close the window afterwards.

STEP 3: Let us test it out!

Open the sheet containing the data. Go to Developer > Code > Macros

Make sure your formula cells and macro are both selected. Click Run.

With just one click, all of the formula cells are now converted to values! The values also changed because of the nature of the RANDBETWEEN formula, as it changes along with any change done to the workbook 🙂

How to Convert Formulas into Values Using Macros In Excel

Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.