Excel VBA:"range.find()"找不到显示为"####"的值 [英] Excel VBA: `range.find()` does not find values which are displayed as `####`

问题描述

我正在尝试在特定列中查找特定值.例如，列B中的值100000.以下代码仅在列足够宽以显示完整数字时才有效:

I'm trying to find a specific value in a specific column. For example the value 100000 in the column B. The following code only works if the column is wide enough to display the full number:

Dim rngSearchRange As Range
Set rngSearchRange = ThisWorkbook.Worksheets(1).Columns(2)
Dim searchTerm As Variant
searchTerm = 100000

Dim rngResultRange As Range       
Set rngResultRange = rngSearchRange.Find(What:=searchTerm, lookin:=xlValues, lookat:=xlWhole)

列变窄后，Excel仅在特定单元格中显示#####而不是100000，查找方法将返回Nothing.

As soon as the column gets to narrow, so Excel only displays ##### instead of 100000 in the specific cell the find-method returns Nothing.

是否有一种方法可以根据实际值而不是根据值的显示使用查找方法?如果不是，是否有For Each cell In rng.Cells的替代品?最终，我正在寻找占用最少资源的方法.

Is there a way to use the find-method based on the actual values and not on the display of the values? If not, are there any alternatives to For Each cell In rng.Cells? Eventually, I'm looing the method which usees up the least resources.

注意:searchRange仅是一列，searchValue不存在或仅存在一次.

Note: the searchRange is only one column, the searchValue either doesn't exist or only exists once.

注意:这里有一个

Note: there is a followup question on using match() Note: from time to time it seems to work although neither data nor code changes. Unfortunately, I can not reproduce the change. This whole thing might be a bug indeed

推荐答案

如果列宽太窄，可以重现Find失败.

Can reproduce the Find failing if the column width is too narrow.

Match没有这个问题.

Sub dural()
    Dim rngSearchRange As Range
    Set rngSearchRange = ThisWorkbook.Worksheets(1).Columns(2)
    Dim searchTerm As Variant
    searchTerm = 100000
    Dim rngResultRange As Range

    Dim found As Variant
    found = Application.Match(searchTerm, rngSearchRange, 0)

    If Not IsError(found) Then
        Set rngResultRange = rngSearchRange.Cells(found)
        MsgBox rngResultRange.Address
    End If
End Sub

根据您的用例，这可能是一个选择，或者如果没有，则可能是

Depending on your use case, this may be an option, or if not, maybe Range.AutoFit? Though with "I'm trying to find a specific value in a specific column," it sounds like this could be an option.

这篇关于Excel VBA:"range.find()"找不到显示为"####"的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！