VBA Range.Find()函数不适用于较大的浮点数 [英] VBA Range.Find() function not working with large floating numbers

问题描述

我正在编写代码以查找列中的最高金额，并取回目标单元格中​​具有9个字符的值以及相邻列中单元格的值

I am writing code to lookup the highest dollar amount in a column and bring back the value as well as the value of cells in adjacent columns with 9 char in the target cell

当我设置范围变量时，它会显示"12000.88"的值，该值可以正常工作，但是，只要该值扩展到9位以上的"123000.55"，"set fnd"就不会找到它，并且范围变量为没有.我测试了许多其他东西，发现唯一的区别是值的长度

When I set the range variable it shows the value of "12000.88" which works fine, however as soon as that value extends to over 9 digits "123000.55" the "set fnd" doesn't find it and the range variable is Nothing. I have tested many other things and have found the only difference is the length of the value

Sub Populate()

    Dim Fnd As Range    
    Set Fnd = Range("L:L").Find(WorksheetFunction.Large(Range("L:L"), 1), , xlValues)

End Sub

我对较小的数字没有问题，但是我不明白为什么对较大的数字不起作用.

I have no problem with smaller numbers but I don't understand why this wouldn't work with larger numbers.

推荐答案

问题出在Excel和VBA使用的不同十进制分隔符中.因此，在浮点值中，存在隐藏特征".实际上，Find()函数会查找string，并且双精度字符串被字符串化"显然不是正确的方法-带逗号而不是点.考虑将其解析为字符串并将逗号替换为一个点以使其起作用:

The problem is in the different decimal separators that Excel and VBA are using. Thus, in the floating point values, there is a "hidden feature". The Find() function actually looks for a string and the double is "stringified" obviously not the correct way - with a comma and not with a dot. Consider parsing to string and replacing the comma with a dot to make it work:

Sub Populate()

    Dim foo As Range        
    Dim biggus As Double

    biggus = WorksheetFunction.Large(Range("A:A"), 1)
    Set foo = Range("A:A").Find(ReplaceCommas(biggus), , xlValues)

    Debug.Print foo.Address

End Sub

Function ReplaceCommas(valueAsString As Variant) As String

    Dim valueToReplace  As String
    valueToReplace = valueAsString
    ReplaceCommas = Replace(valueAsString, ",", ".")

End Function

当在SearchIn参数中使用xlFormulas时，功能"未出现的事实是由于xlFormulas将单元格的内容转换为字符串，以便搜索它.例如，如果有=SUM(A1+A2)并且我们正在寻找1+A，找到它的唯一方法是将单元格的内容解析为String.

The fact that the "feature", does not appear, when xlFormulas, is in the SearchIn parameter, is due to the fact, that xlFormulas converts the content of the cell to string, in order to search in it. E.g., if there is =SUM(A1+A2) and we are looking for 1+A, the only way to find it is to parse the content of the cell to String.

这篇关于VBA Range.Find()函数不适用于较大的浮点数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！