Excel VBA: `range.find（）`无法查找显示为“＃＃＃＃”的值

Question

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

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。

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

注意：searchRange只有一列，searchValue要么不存在，要么只存在一次。

注意：有一个关于使用match()的后续问题。 注：有时它似乎能工作，尽管数据和代码都没有更改。不幸的是，我无法重现这个变化。整件事可能确实是一个错误。

- Albin

---

可以重现“查找”失败。自动调整列宽是否可行？ - BigBen

@ScottCraner 我无法去掉 lookat:=xlWhole（因为它不会给我正确的结果），但我尝试添加了 lookin:=xlValues - 没有改变。 - Albin

@Albin - 你尝试过使用“Match”提出的任何答案吗？顺便说一句 - 这是在“Find”中发现潜在错误的好方法。 - BigBen

经过测试，我发现LookIn:=xlFormulas在所有情况下都有效。如果您没有指定此参数，它会继承上次使用时的设置。 - SJR

@Chronocidal，基本上和我之前在SJR评论中提到的问题一样。 - Albin

Answer 1

如果列宽过窄，可以重现“查找”失败。匹配没有这个问题。

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

根据您的使用情况，这可能是一个选择，或者如果不行，也许 Range.AutoFit 是一个选择？但是，根据 "我正在尝试在特定列中查找特定值"，这似乎是一个选择。

- BigBen

---

谢谢，我会尝试一下。我想在使用range.find()之前，我也可以尝试使用VBA来调整列宽（然后再将其恢复到原始宽度），但这似乎不是一个好的解决方案，因为它取决于值的“呈现”，而这是我无法控制的。至少我必须确保在调整列宽后，所有值都完全可见。有什么办法可以做到这一点吗？ - Albin

你可以使用“查找”功能查找“＃”，但我不确定它是否稳健。另外，FYI，似乎自适应会取消隐藏的列。 - BigBen

我会尝试一下，你知道除了“#”以外还有什么其他可以显示在实际值之外的东西吗？ - Albin

科学计数法，继续以示例 1E+05 为例。这就是为什么我不确定这是否是个好主意。 - BigBen

在我的情况下，我可以寻找“+”和“#”，因为所涉及的范围仅包含数字值。我会尝试一下。 - Albin

Answer 2

你可以将范围转换成数组并循环遍历数组，或者直接使用MATCH：

    Sub test()
        Dim rngSearchRange, rngResultRange As Range
        Dim searchTerm As Variant
        Dim vRow As Variant

        Set rngSearchRange = ThisWorkbook.Worksheets(1).Columns(2)
        searchTerm = 10000
        vRow = Application.Match(searchTerm, rngSearchRange, 0)
        If Not IsError(vRow) Then
            Set rngResultRange = rngSearchRange.Resize(1, 1).Offset(vRow - 1, 0)
        Else
            MsgBox "Not Found"
        End If
End Sub

- Charles Williams

---

谢谢，但是循环需要太多时间，正如我在问题中提到的那样。现在我没有看到与BigBen的答案（使用match）有任何区别，或者我错过了什么吗？ - Albin

@BigBen 是的，但如果我没记错的话，填充数组仍需要循环遍历每个单元格，所以最终我还是要循环两次（一次遍历单元格，一次遍历数组）！？或者有更快的方法吗？Charles 的匹配示例本质上与你的相同，对吧？ - Albin

你可以用一行代码将范围读入数组中。在这里查看一个很好的解释。遍历数组非常快。逐个访问每个单元格才是慢的。 - BigBen

@Albin 你是否可以只循环 rngSearchRange .Columns（或者 rngSearchRange .Rows - 取决于哪个更小！），并在每个上执行 Match 直到找到结果？ - Chronocidal

@Charles Williams：为什么你的例子要调整大小？一旦我有了搜索结果/单元格，我就不需要再调整大小了（如果没有这个步骤，这个例子会更容易理解）。 - Albin

Answer 3

find 函数的问题在于它仅查找某些原因下显示的值，与按下或单击“查找和选择”选项所获得的搜索框的行为相同。目前还没有已知方法来解决这个问题（如评论所指出的，在 xlValues 和类似内容中查找）。

由于有多种方法可以解决此问题，最可靠但速度最慢的方法是使用foreach循环：

For Each cel In rngSearchRange
If cel.Value = searchTerm Then
Set rngResultRange = cel
exit for '<-If you want the first result, leave this. If you want the last result, omit. Using the first result could be significantly quicker as it will stop looping right away.
End If
Next cel

请确保将范围设置为明确的值，比如Range("A1:B87")，而不是Columns(2)，否则会引发类型不匹配错误。如果你想搜索B列，请使用Range("B:B")。

- Plutian

---

我认为，将范围读入数组并在数组上使用循环（正如在不同答案中已经建议的那样）应该更快。不过我还需要测试一下。 - Albin

那确实是我的其中一个想法，而且可能确实会稍微快一些。但我没有大型数据集来测试它，所以我选择了我确定的方法。如果你尝试了两种方法，能否告诉我哪个更快？ - Plutian

当然，我会这样做。 - Albin

Answer 4

这是一个作弊版本：它将范围复制到一个临时工作表中，将公式转换为值，并在那里进行查找。

Public Function FindValueInRange(ByVal RangeToSearch As Range, ByVal ValueToFind As Variant) As Range
    Dim WasActive As Worksheet, ScreenUpdating As Boolean, Calculation As XlCalculation
    'Store current position
    Set WasActive = ActiveSheet
    ScreenUpdating = Application.ScreenUpdating
    Application.ScreenUpdating = False
    Calculation = Application.Calculation
    Application.Calculation = xlCalculationManual

    'Let's get to work!
    Set FindValueInRange = Nothing 'Default to Nothing
    On Error GoTo FunctionError

    Dim TempSheet As Worksheet, FoundCell As Range, DisplayAlerts As Boolean

    'Create Temp Sheet
    Set TempSheet = Worksheets.Add

    'Copy data to Temp Sheet, in the same location
    TempSheet.Range(RangeToSearch.Address(True, True, xlA1, False)).Value = RangeToSearch.Value

    'Column Width to Maximum!
    TempSheet.Range(RangeToSearch.Address(True, True, xlA1, False)).EntireColumn.ColumnWidth = 255

    'Search the cells in the Temp Sheet
    Set FoundCell = TempSheet.Range(RangeToSearch.Address(True, True, xlA1, False)).Find(ValueToFind, LookIn:=xlFormulas, LookAt:=xlWhole)

    'Return the found cell, but on the original Worksheet
    If Not (FoundCell Is Nothing) Then Set FindValueInRange = RangeToSearch.Worksheet.Range(FoundCell.Address(True, True, xlA1, False))

    'Remove the Temp Sheet
    DisplayAlerts = Application.DisplayAlerts
    Application.DisplayAlerts = False
    TempSheet.Delete
    Application.DisplayAlerts = DisplayAlerts
    Set TempSheet = Nothing

FunctionError:
    On Error GoTo -1 'Reset the error buffer
    'Restore previous position
    WasActive.Activate
    Application.Calculation = Calculation
    Application.ScreenUpdating = ScreenUpdating
End Function

这将会像这样使用：

Set rngResultRange = FindValueInRange(rngSearchRange, searchTerm)

- Chronocidal

---

谢谢，虽然这是一个摆脱公式的好方法，但它并没有解决最初的问题。 - Albin

@Albin 因为它会去掉公式，所以可以使用 LookAt:=xlFormulas（无论显示宽度如何都应该有效）。或者，您可以将 EntireColumn.ColumnWidth=255 设置为使列变得非常宽？ - Chronocidal

我不认为 LookAt:=xlFormulas 解决了问题，但我会再试一次。但我认为我更喜欢使用 match 的解决方案，因为它更容易实现，而且不需要太多的处理时间/内存（至少对于这个情况/问题而言）。 - Albin

Answer 5

试试这个：

Sub test()
   Dim rngSearchRange, rngResultRange As Range
   Dim searchTerm As Variant

   Set rngSearchRange = ThisWorkbook.Worksheets(1).Columns(2)
   searchTerm = 10000
   Set rngResultRange = rngSearchRange.Find(what:=searchTerm, LookIn:=xlValues)
End Sub

- CADV

---

我没有看到我的代码有任何区别（除了你将rngSearchRange定义为Variant）？！而且将其定义为Variant也没有帮助，为什么会有帮助呢？ - Albin

与您最初的问题相比，我在两个区间都使用了“set”，并更改了find的参数。我对一些数据进行了测试，它能正常工作，所以我希望它也能适用于您的数据。此外，如果您阅读代码，rngSearchRange声明为一个Range，而不是Variant。 - CADV

谢谢！缺失的集合只是在我的问题中缺失，而不是在我的原始代码中缺失，所以那不是问题（我已经纠正了我的问题）。我忽略了参数，但实际上，我不能使用你的参数，因为它不包含lookat=xlWhole。我需要这个来进行搜索（请参见我的问题）。没有rngSearchRange将被声明为Variant，请参见此处，或者他们是否改变了VBA的语法？ - Albin

@CADV Albin是正确的，你必须明确声明变量类型，否则它就是一个“Variant”：Dim rngSearchRange as Range, rngResultRange as Range。 - BigBen