以帮助
我想根据第二个单元格的小数位数更改范围的格式。知道吗?我正在使用以下函数来计算小数(作品)
Function CountDecimalPlaces(aNumber As Double) As Long
Dim len1 As Long, len2 As Long
len1 = Len(CStr(aNumber))
len2 = Len(CStr(Int(aNumber)))
CountDecimalPlaces = len1 - len2 + CLng(len1 <> len2)
End Function
并希望使用它来用不同的小数位格式化我的范围
For b = 1 To lastCol
Range(cells(3,b),cells(50,b)).NumberFormat = "0." & CountDecimalPlaces (Cells(2, b)) x 0
Next b
我当然知道"计数十进制位置(单元格(2,b))x 0"不起作用,但我希望它能让您理解
将你的代码替换为:
Range(cells(3,b),cells(50,b)).NumberFormat = "0." & String(CountDecimalPlaces(Cells(2, b)), "0")
String
采用两个强制参数:
Number
:一个字符必须重复的次数Character
:必须重复的字符
这是计算数字中小数位数的另一种方法:
Function CountDecimalPlaces(aNumber As Double) As Long
CountDecimalPlaces = Len(Split(CStr(aNumber), ".")(1))
End Function
编辑(基于Excelosaurus的建议):
Function CountDecimalPlaces(aNumber As Double) As Long
If Int(aNumber) = aNumber Then
CountDecimalPlaces = 0
Else
CountDecimalPlaces = Len(Split(CStr(aNumber), Application.International(xlDecimalSeparator))(1))
End If
End Function
您也可以按如下方式修改计数小数。
Function CountDecimalPlaces(aNumber As Double) As String
Dim len1 As Long, len2, lenX As Long
Dim i As Integer
Dim answer As String
answer = "0."
len1 = Len(CStr(aNumber))
len2 = Len(CStr(Int(aNumber)))
lenX = len1 - len2 + CLng(len1 <> len2)
If lenX > 0 Then
For i = 1 To lenX
answer = answer & "0"
Next i
End If
CountDecimalPlaces = answer
End Function
并在您的主函数中使用如下:
Range(cells(3,b),cells(50,b)).NumberFormat = CountDecimalPlaces (Cells(2, b))