VBA Range.Find函数对舍入的负数 [英] VBA Range.Find function on rounded negative numbers

问题描述

我写的VBA宏出现问题，该部分旨在查找范围内的最小值.该行如下所示:

I'm having an issue with a VBA macro I wrote, with the part that is intended to find a lowest value in range. The line looks like this:

Min = Application.WorksheetFunction.Min(a0eqB) Set MinCell = a0eqB.Find(Min, LookIn:=xlValues)

Min = Application.WorksheetFunction.Min(a0eqB) Set MinCell = a0eqB.Find(Min, LookIn:=xlValues)

，它返回Object variable or With block variable not set错误.现在，我知道为什么会发生这种情况-有时Find找到Nothing，并且我学会了如何处理这种情况.虽然，当单元格中已支付的值与Min变量中的值不同时，也会找到Nothing.

And it returns Object variable or With block variable not set error. Now, I know why this happens - sometimes Find finds Nothing, and I learned how to handle this type of situations. Although, it also finds Nothing when the value that is dispayed in a cell differs from the value in Min variable.

例如，当Min = -11.2641373534338时，单元格中的值为-11.264137，然后会发生错误.但是，如果我通过Excel UI按钮更改为该单元格显示的小数位数，直到它为-11.2641373534338，则一切正常.如果有帮助，单元格中的值实际上是公式计算结果.

For instance, when Min = -11.2641373534338, the value in a cell is -11.264137, and then error occurs. But if I change the number of decimal places displayed for that cell via Excel UI buttons until it is -11.2641373534338, everything works fine. Value in a cell is actually a formula calculation result if that helps.

我看到两种处理此问题的方法:

I see two ways of dealing with this issue:

1. 找出要显示的小数位数，然后舍入实际值以使它们匹配.但是这种方式的匹配实际上可能会出错，因为如果将11.321和11.322之类的数字四舍五入到小数点后两位，则它们将相同.另外，由于我需要找到单元格的地址才能做到这一点，因此也是不可能的，并且在上面代码的第2行中完成了操作，这导致了问题.
2. 以某种方式告诉Find函数使用实际的和未显示的数字，但是我不知道该怎么做.我用Google搜索了几天，但仍然没有成功.

1. Finding out how many decimal places are being displayed and then rounding actual value so they match. But this way matching actually might go wrong, since numbers like 11.321 and 11.322 if rounded to 2 decimal places will be the same. Also, it's not possible due to the fact that I need to find the cell's adress in order to do that, and that's done in line 2 of the code above that's causing an issue.
2. Somehow telling Find function to use actual and not displayed numbers, but I've got no idea on how to do that. I googled for several days but still no success.

非常感谢您的帮助.

推荐答案

研究了负数的问题并将其与find函数一起使用；一种可能的解决方案是格式化搜索范围和数字，以使用相同的数字格式进行查找. (基于@Gary学生提供的代码)

Having looked at the issues with negative numbers and using it with the find function; a possible solution would be to format your search range and number to find with the same number format. (Based on code supplied by @Gary's Student)

代码在下面.

如果您不想更改电子表格中的数字格式，那么另一种方法是制作工作表的副本以进行格式化并找到地址，然后删除副本并使用原始表中的地址，但这会涉及更多的编码...

If you don't want to alter the number formats in your spreadsheet, then a further hack would be to make a copy of the worksheet to format and find the address then delete the copy and use the address in the original sheet, but that would involve a bit more coding...

Sub GetLowest()

    Dim sFormat As String: sFormat = "0.000000000"
    Dim wf As WorksheetFunction: Set wf = Application.WorksheetFunction
    Dim rng As Range: Set rng = Range("MinRange")
    rng.NumberFormat = sFormat
    Dim Lowest As Double: Lowest = Format(wf.Min(rng), sFormat)
    Dim WhereIs As Range: Set WhereIs = rng.Find(What:=Lowest)

    MsgBox WhereIs.Address

End Sub

这篇关于VBA Range.Find函数对舍入的负数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！