时间戳

首页 > 解决方案 > 如何在 Double 上始终保留 2 位小数

excel - 如何在 Double 上始终保留 2 位小数

问题描述

我有一个宏，它遍历文本列表，提取美元金额，将它们增加 12%，然后用更新的美元金额替换文本。

这是几行数据的样子： 在此处输入图像描述

这是我运行宏后的结果： 在此处输入图像描述

例如，我需72.8要这样做72.80。

有时结果只有 1 位小数，有时只有 3 位。该Round函数对我来说很好，可以将结果截断到小数点后 2 位，但无助于添加 0 以将数字保持在小数点后两位。

如果结果只有一个小数位，我需要一种方法来用 0 填充第二个小数位。

这是宏：

Function onlyDigits(s As String) As String
' Variables needed (remember to use "option explicit").   '
Dim retval As String    ' This is the return string.      '
Dim i As Integer        ' Counter for character position. '

' Initialise return string to empty                       '
retval = ""

' For every character in input string, copy digits to     '
'   return string.                                        '
For i = 1 To Len(s)
    If Mid(s, i, 1) >= "0" And Mid(s, i, 1) <= "9" Then
        retval = retval + Mid(s, i, 1)
    End If
Next

' Then return the return string.                          '
onlyDigits = retval
End Function

Sub ChangeDollarAmount()

Dim qtyspec As String
Dim previousDollarIndex As Integer
Dim dollarSignCount As Integer
Dim dollarString As String
Dim originalDollarAmount As String
Dim changedDollarAmount As Double
Dim isANumber As Boolean

previousDollarIndex = 1

' row count
lastrow = ActiveSheet.Range("A1").CurrentRegion.Rows.Count

For Each cell In Range("K2:K" & lastrow)
    Debug.Print cell.Formula

    previousDollarIndex = 1
    qtyspec = cell.Formula
    dollarSignCount = (Len(cell.Formula) - Len(Replace(cell.Formula, "$", ""))) / Len("$")

    ' loop through dollar amounts in text
    For i = 1 To dollarSignCount
        isANumber = False
        previousDollarIndex = InStr(previousDollarIndex + 1, cell.Formula, "$")
        originalDollarAmount = Mid(cell.Formula, previousDollarIndex, 8)

        Do While isANumber = False
            If Not IsNumeric(Right(originalDollarAmount, 1)) Then
                originalDollarAmount = Left(originalDollarAmount, Len(originalDollarAmount) - 1)
            Else
                isANumber = True
            End If
        Loop

        ' extract only digits from dollar amount ($345.23 -> 34523)
        dollarAmount = onlyDigits(originalDollarAmount)

        ' add decimal point and increase dollar amount by 12% (34523 -> 345.23 -> 386.66)
        changedDollarAmount = Round(CDbl(dollarAmount) * 1.12 * 0.01, 2)

        ' update the dollar amount in the text
        cell.Formula = Replace(cell.Formula, originalDollarAmount, "$" + CStr(changedDollarAmount))
    Next i

Next cell

End Sub

标签： excelvba

解决方案

changedDollarAmount = CDbl(dollarAmount) * 1.12 * 0.01

cell.Formula = Replace(cell.Formula, originalDollarAmount, Format$(changedDollarAmount, "$0.00"))

推荐阅读