时间戳

首页 > 解决方案 > 如何在VBA中比较数字到一定的小数位数？

excel - 如何在VBA中比较数字到一定的小数位数？

问题描述

我有下面的代码来比较同一工作簿中两个不同工作表的值，但我只需要比较到小数点后 2 位，即 123.45 而不是 123.4568786。有没有办法指定十进制值的数量进行比较？

difference = 0
For col = 1 To maxcol
For row = 1 To maxrow
    colval1 = ""
    colcal2 = ""
    colval1 = Ws1.Cells(row, col)
    colval2 = Ws2.Cells(row, col)
        If colval1 <> colval2 Then
            difference = difference + 1
            Cells(row, col) = colval1 & "<>" & colval2
        End If
Next row
Next col

这是代码的输出：

在此处输入图像描述

编辑：我声明colval1andcolval2为 Double，但我仍然得到“类型不匹配”，但使用这一行colval1 = Ws1.Cells(row, col)

difference = 0
For col = 1 To maxcol
For row = 1 To maxrow
    colval1 = 0
    colcal2 = 0
    colval1 = Ws1.Cells(row, col)
    colval2 = Ws2.Cells(row, col)
        If WorksheetFunction.Round(colval1, 2) <> WorksheetFunction.Round(colval2, 2) Then
            difference = difference + 1
            Cells(row, col) = colval1 & "<>" & colval2
        End If
Next row
Next col

标签： excelvba

解决方案

You can use the VBA's round function which returns the rounded number based on the specified number of decimal places

    If Round(colval1, 2) <> Round(colval2, 2) Then
        difference = difference + 1
        Cells(Row, col) = Round(colval1, 2) & "<>" & Round(colval2, 2)
    End If

or

    colval1 = Round(Ws1.Cells(Row, col), 2)
    colval2 = Round(Ws2.Cells(Row, col), 2)
    If colval1 <> colval2 Then
        difference = difference + 1
        Cells(Row, col) = colval1 & "<>" & colval2
    End If

regarding the right annotation of @BigBen, we can define a small function that returns the desired value without rounding

Public Function Round2(Num As Variant, Decimals As Variant) As Variant
    
        Round2 = IIf(IsNumeric(Num) And IsNumeric(Decimals), Int(Num * 10 ^ Decimals) / 10 ^ Decimals, CVErr(xlErrValue))
    
End Function

推荐阅读