构建宏以将公式转换为值，但使用过滤器 [英] Building a macro to convert formulas to values, but with a filter on
问题描述
您好，
我正在尝试创建一个宏，它将遍历一系列单元格并将公式转换为这些单元格中的值。这是一个快速的小宏，其他人写的，我发现我想建立：
I'm trying to create a macro that will go through a range of cells and convert formulas to values in those cells. Here's a quick little macro that someone else wrote that I found that I would like to build off of:
Sub ValuesOnly()
Dim rRange As Range
'www.ozgrid.com
On Error Resume Next
Set rRange = Application.InputBox(Prompt:="Select the formulas", Title:="VALUES ONLY", Type:=8)
If rRange Is Nothing Then Exit Sub
rRange = rRange.Value
问题是，人们会在选择了一系列细胞的过滤器时使用这个宏...过滤掉的不应该更改为值。这个想法是一堆行，其中包含一个小计行，然后是另一个带有小计行的
行，等等。我希望能够将非小计的单元格更改为.value，但将小计的单元格保留为公式。如果没有用户输入单独的范围，我想不出这样做的方法（对于
例如，A1：A50，A52：A102，A104：A154等）。
The problem is, people will be using this macro when they have a filter on the range of cells selected... The cells that are filtered out should not be changed to values. The idea is a bunch of rows that have a subtotal line, then another bunch of rows that have a subtotal line, and so forth. I would like to be able to change the non-subtotal cells to .value, but leave the subtotal cells as a formula. I cannot think of a way to do this without having the user input separate ranges (for example, A1:A50, A52:A102, A104:A154, etc).
非常感谢任何帮助/想法。如果您需要进一步澄清我想要或完成的内容，请告诉我。谢谢！
Any help/ideas would be greatly appreciated. Let me know if you need further clarification on what I'm trying to ask or accomplish. Thank you!
推荐答案
你好，
你可以试试这个：
Sub ValuesOnly（）
Sub ValuesOnly()
Dim rRange As Range
Dim rRange As Range
Dim i As Long
Dim j As Long
Dim RowCnt As long
Dim ColCnt As Long
'www.ozgrid.com
'www.ozgrid.com
On Error Resume Next
On Error Resume Next
设置rRange = Application.InputBox（提示：="选择公式"，标题：="仅限值"，输入：= 8）
Set rRange = Application.InputBox(Prompt:="Select the formulas", Title:="VALUES ONLY", Type:=8)
如果rRange没什么，那么退出子
If rRange Is Nothing Then Exit Sub
'rRange = rRange.Value
RowCnt = UBound（rRange.FormulaR1C1）
ColCnt = UBound（rRange.FormulaR1C1,1）
对于j = 1 To RowCnt
适用于k = 1至ColCnt
如果是左（rRange.FormulaR1C1（j，k），5）<> " = SUM（" Then
rRange.Cells（j，k）= rRange.Cells （j，k）。值
结束如果
下一个k
下一个j
结束子
'rRange = rRange.Value
RowCnt = UBound(rRange.FormulaR1C1)
ColCnt = UBound(rRange.FormulaR1C1, 1)
For j = 1 To RowCnt
For k = 1 To ColCnt
If Left(rRange.FormulaR1C1(j, k), 5) <> "=SUM(" Then
rRange.Cells(j, k) = rRange.Cells(j, k).Value
End If
Next k
Next j
End Sub
基本上，它会循环遍历范围内的所有单元格 - 如果公式是SUM（总计，小计），它将跳过单元格，否则单元格公式将是如果您计算总计类似于：= A1 + A10 + A30，那么它将涉及
更多的定制。
Basically, it would loop through all cells in the range - and if the formula is a SUM (totals, subtotals), it would skip the cell, otherwise cell formula would be changed by value. If you calculate totals similar to: =A1+A10+A30, then it would involve a lot more customization.
Nadia
这篇关于构建宏以将公式转换为值，但使用过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
