单元格是:
3.141516
=10/6
=rand()
or blank
etc...
结果:
=ROUND(3.141516,1)
=ROUND(10/6,1)
=ROUND(RAND(),1)
并且如果为空-留空(不是ROUND(,1))
我想通过InputBox或其他方式选择范围和小数
我找到了如何在公式周围,常量周围,空白单元格,输入框周围添加ROUND()的方法,但全部都在单独的代码中(而不是一起)。我不是vba英雄,所以我需要帮助。谢谢你 :)
Sub RoundNum()
Dim Rng As Range
Dim WorkRng As Range
Dim xNum As Integer
On Error Resume Next
xTitleId = "Round Numbers"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xNum = Application.InputBox("Decimal", xTitleId, Type:=1)
For Each Rng In WorkRng
Rng.Value = Application.WorksheetFunction.Round(Rng.Value, xNum)
Next
End Sub
Sub Makro1()
Dim Str As String
For Each cell In Selection
Str = cell.FormulaR1C1
If Mid(Str, 1, 1) = "=" Then Str = Mid(Str, 2)
cell.FormulaR1C1 = "=ROUND(" & Str & ",1)"
Next cell
End Sub
最后,我做了这样的事情:
Sub rRoundIt()
Dim rng As Range
Dim rngArea As Range
Dim AppCalc As Long
On Error Resume Next
With Application
AppCalc = .Calculation
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
Set rng = Union(Selection.SpecialCells(xlCellTypeFormulas, xlNumbers), _
Selection.SpecialCells(xlCellTypeConstants, xlNumbers))
For Each rngArea In rng
If Left(rngArea.Formula, 7) <> "=ROUND(" Then _
rngArea.Formula = "=ROUND(" & Replace(rngArea.Formula, Chr(61), vbNullString) & ", 1)"
Next rngArea
With Application
.ScreenUpdating = True
.Calculation = AppCalc
End With
End Sub
谢谢吉普:)
此简短子项同时使用Range.SpecialCells方法和xlCellType Enumeration中的xlCellTypeConstants和xlCellTypeFormulas选项。.SpecialCells仅通过使用xlNumbers选项获得那些产生数字的常数或公式来进一步过滤。
Sub roundIt()
Dim r As Range, rng As Range
With Worksheets("Sheet1")
With .UsedRange.Cells
Set rng = Union(.SpecialCells(xlCellTypeFormulas, xlNumbers), _
.SpecialCells(xlCellTypeConstants, xlNumbers))
For Each r In rng
If Left(r.Formula, 7) <> "=ROUND(" Then _
r.Formula = "=ROUND(" & Replace(r.Formula, Chr(61), vbNullString) & ", 1)"
Next r
End With
End With
End Sub
理想情况下,如果工作表的.UsedRange属性中没有表示数字的公式或常量,则最好提供一些错误控制。如果找不到,则.SpecialCells将返回nothing
。
通过仅关注那些可能具有数值以应用ROUND函数的单元格,您应该大大缩短遍历工作表中单元格的循环迭代。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句