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 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 to get those values.
- On the Developer Tab, select the Visual Basic option under the Code section.
- 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.
- You have to open the sheet containing the data. On the Developer Tab, choose the Macros option in the Code section.
- You need to make sure that your macro is selected and click the Run button.
- At last, you can see that all the formula cells are converted into values as shown in the below screenshot.
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. Keep Learning!
Top comments (0)