Excel converts function calculation results of multiple worksheets into numerical values

This article brings you relevant knowledge about excel, which mainly organizes the related issues of how to convert the function calculation results of multiple worksheets into numerical values, including the functions of a single worksheet. Converting formulas to numerical values, converting formulas to numerical values ​​in multiple worksheets, converting formulas to numerical values ​​in multiple workbooks, etc. Let’s take a look at them together. I hope it will be helpful to everyone.

Related learning recommendations: excel tutorial

How to convert the function calculation results of multiple worksheets into numerical values? Today I will focus on this type of issue with you. Depending on the operating scope, this can be divided into three situations.

• Convert the formula to a value in a single worksheet

• Convert the formula to a value in multiple worksheets

• Converting formulas to numerical values ​​in multiple workbooks

1. Converting regional formulas to numerical values

Let’s talk about the first case first, converting the regional function calculation result to a numerical value. This is the most common and the operation is relatively simple. Copy the target area, right-click and paste selectively as a value.

The operation animation is as follows:

2. Convert multi-sheet formulas to numerical values

If you want to convert all worksheets in the current workbook To convert the formula into a numerical value, you need to use VBA code. The reference code is as follows:

Sub FunctionTransValue_Sheets()
Dim sht As Worksheet
For Each sht In Worksheets '遍历工作表
sht.UsedRange.Value = sht.UsedRange.Value
Next
End Sub

The 3rd to 5th lines of code traverse the worksheet, and the 4th line of code changes the value of the cell to the value, which removes the formula content.

3. Convert formulas to values ​​in multiple workbooks

To convert formulas to values ​​in multiple workbooks under a specified folder, you can copy and run the following code:

Sub FunctionTransValue_Workbooks() '全部工作簿
Dim strPath As String, sht As Worksheet
Dim strWbName As String, wb As Workbook
With Application.FileDialog(msoFileDialogFolderPicker) '获取文件夹路径
If .Show Then strPath = .SelectedItems(1) & "\" Else Exit Sub
End With
On Error Resume Next
With Application
.ScreenUpdating = False '取消屏幕刷新
.DisplayAlerts = False '取消警告信息
.EnableEvents = False '取消事件
.Calculation = xlCalculationManual '取消公式重算
.AskToUpdateLinks = False '取消外链询问
End With
strWbName = Dir(strPath & "*.xls*")
Do While strWbName <> "" &#39;dir语句遍历excel文件
If strWbName <> ThisWorkbook.Name Then
Set wb = Workbooks.Open(strPath & strWbName) &#39;打开工作簿
For Each sht In wb.Worksheets &#39;遍历工作表公式转数值
sht.UsedRange.Value = sht.UsedRange.Value
Next
wb.Close True &#39;保存关闭工作簿
End If
strWbName = Dir() &#39;下一个excel文件
Loop
With Application &#39;恢复系统设置
.ScreenUpdating = True
.DisplayAlerts = True
.EnableEvents = True
.Calculation = xlCalculationAutomatic
.AskToUpdateLinks = True
End With
If Err.Number Then
MsgBox Err.Description
Else
MsgBox "转换完成。"
End If
End Sub

The code is analyzed as follows

Lines 4 to 6 of code allow the user to select a target folder and get the path to the folder.

Lines 8 to 14 cancel a series of system settings.

Lines 15 to 25 of code use the conditional loop statement DIR function to traverse the workbook. Lines 19 to 21 of code then traverse the worksheet and convert the formula into a numerical value.

Lines 26 to 32 of code restore a series of system settings.

Lines 33 to 37 code feedback the program running results.

Related learning recommendations: excel tutorial

The above is the detailed content of Excel converts function calculation results of multiple worksheets into numerical values. For more information, please follow other related articles on the PHP Chinese website!