Convert Formulas into Values using Macros (VBA) in Excel 365!!
Tags: convertformulasintov, vbamacros
Published: 2021-02-13 06:50:18

---
title: Convert Formulas into Values using Macros (VBA) in Excel 365!!
published: true
date: 2021-02-12 09:11:25 UTC
tags: Convertformulasintov,VBAMacros
canonical_url: https://geekexcel.com/convert-formulas-into-values-using-macros-vba-in-excel-365/
---

In the Excel workbook, we have lots and lots of cells with formulas. Sometimes we want to change them into permanent values. To do this, we come up with Macros and [VBA](https://geekexcel.com/excel-office-365-vba-overview-definition-and-examples/) concept in Excel. Macros will make this work as simple as possible, so you can finish the task with a few clicks. In this post, we are going to see how to **convert formulas into values using Macros** (VBA) in Excel Office 365.

## Convert Formulas into Values using Macros:

If you want to change the formulas into permanent values in the worksheet, follow the below steps.

- In the below screenshot, you can see the example input range from cell **B1:B5**. Each cell in the range contains a formula, we have used [RAND Function](https://geekexcel.com/how-to-use-excel-rand-function-in-office-365-with-examples/) to get those values.

![Example range](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/02/EXCEL_QP3upkkMeJ.png)<figcaption id="caption-attachment-21771">Example range</figcaption>

- On the **Developer Tab, select** the **Visual Basic** option under the **Code** section.

![Select Visual Basic option](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/09/Select-Visual-Basic-option-4.png)<figcaption id="caption-attachment-14755">Select the Visual Basic option</figcaption>

- Then you need to copy and paste the code given below.

VBA code to convert formulas into values using Macros

Sub Convert()

Dim rng As Range

Dim formulaCell As Range

Set rng = Selection

For Each formulaCell In rng

If formulaCell.HasFormula Then

formulaCell.Formula = formulaCell.Value

End If

Next formulaCell

End Sub

- You need to **save the code** by selecting it. Then **close** the **window.**

![Enter the code](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/02/EXCEL_IRFisxvNXl.png)<figcaption id="caption-attachment-21772">Enter the code</figcaption>

- You have to open the sheet containing the data. On the **Developer Tab, choose** the **Macros option** in the **Code** section.

![Choose the Macros option](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2020/09/Choose-the-Macros-option-2.png)<figcaption id="caption-attachment-14747">Choose the Macros option</figcaption>

- You need to make sure that your macro is selected and **click** the **Run** button.

![Click Run button](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/02/EXCEL_H6OLImzffE.png)<figcaption id="caption-attachment-21774">Click Run button</figcaption>

 

- At last, you can see that all the formula cells are converted into values as shown in the below screenshot.

![Output](https://eadn-wc04-371788.nxedge.io/cdn/wp-content/uploads/2021/02/EXCEL_wazNNxVFUV.png)<figcaption id="caption-attachment-21775">Output</figcaption>

## Wind-Up:

In this post, we have illustrated the simple steps to **convert formulas into values using Macros** (VBA) in Excel Office 365. Share your **feedback** in the below comment section. Thanks for visiting **[Geek Excel.](https://geekexcel.com/)** Keep Learning!

#### Related Articles:

- [Remove Extra Spaces from Selection Using Macros (VBA) in Excel!!](https://geekexcel.com/remove-extra-spaces-from-selection-using-macros-vba-in-excel/)
- [Add a Degree Symbol to Selection Range using Macros (VBA) in Excel!](https://geekexcel.com/add-a-degree-symbol-to-selection-range-using-macros-vba-in-excel/)