VBA 查找函数找不到给定值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20152328/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA find function can't find given value
提问by user2844154
I have written a program that, at one point, searches a row for a given vendor name, and then pulls that vendor's project cost from that column. The vendor name in the spreadsheet is pulled directly from a cover page, and the program reads the vendor name from that same source. This program is in 80+ separate excel files, and works beautifully in almost every single one. However, there are two or three that can't find any of the values in the given range, despite the fact that I am looking right at the value that I'm trying to search for. I tried manually using the find function in Excel, and it fails to find the string as well. What could be preventing Excel from finding these values?
我编写了一个程序，在某一时刻搜索给定供应商名称的行，然后从该列中提取该供应商的项目成本。电子表格中的供应商名称直接从封面中提取，程序从同一来源读取供应商名称。该程序包含在 80 多个单独的 excel 文件中，并且几乎在每个文件中都能完美运行。然而，有两三个在给定范围内找不到任何值，尽管我正在寻找我正在尝试搜索的值。我尝试手动使用 Excel 中的 find 函数，它也找不到字符串。什么可能阻止 Excel 找到这些值？
With Worksheets("Decision Matrix Summary")
m = 0
For Each c In .Range("N23:N42")
If c.Value <= 10 And c.Value > 0 Then
m = m + 1
vendor = c.Offset(0, -13).Value
Worksheets("Supplier Cost Comparison").Cells(2, m + 1) = vendor
Set cell = Worksheets("Bid Tabulation Summary").Rows("9").Find(what:=vendor, after:=Worksheets("Bid Tabulation Summary").Cells(9, 1), _
LookIn:=xlValues, lookat:=xlPart, searchorder:=xlByColumns, searchdirection:=xlNext, _
MatchCase:=False, searchformat:=False)
If Not cell Is Nothing Then
bottomcell = cell.Offset(60000, 0).Address
cell = Worksheets("Bid Tabulation Summary").Range(bottomcell).End(xlUp).Address
Worksheets("Supplier Cost Comparison").Cells(3, m + 1).Value = Worksheets("Bid Tabulation Summary").Range(cell).Value
Else
MsgBox ("A vendor could not be found in the Bid Tabulation Summary. Please refer to the Supplier Cost Comparison tab to determine which vendor.")
Error = "Very Yes"
Exit Sub
End If
Else: End If
Next
End With
Any help is much appreciated. Thanks!
任何帮助深表感谢。谢谢！
EDIT: I fixed it. I cleared the formatting from the cells where I want to find the values, and then re-entered the formulas. Is there a format that prevents a cell from being searched? I set the function to ignore formatting, so to my knowledge, that shouldn't have been an issue. I would appreciated any information that anyone has about that.
编辑：我修好了。我清除了要查找值的单元格的格式，然后重新输入公式。是否有阻止搜索单元格的格式？我将函数设置为忽略格式，所以据我所知，这应该不是问题。我将不胜感激任何人对此的任何信息。
回答by user2844154
I fixed it. I cleared the formatting from the cells where I want to find the values, and then re-entered the formulas. The find function in both Excel and VBA successfully locate the values in the search range, and return the correct location of that cell via my program.
我修好了它。我清除了要查找值的单元格的格式，然后重新输入公式。Excel 和 VBA 中的 find 函数都成功找到了搜索范围内的值，并通过我的程序返回了该单元格的正确位置。
回答by PermaNoob
I know this is an old question, but it was one of the things I came upon when looking for a solution to the same problem. It isn't the formats that are causing the issue. For some reason, Excel is not recognizing the contents of the cells as values. I don't even have any formulas in my range, but it won't recognize some of them as a value.
我知道这是一个老问题，但这是我在寻找同一问题的解决方案时遇到的一件事。这不是导致问题的格式。出于某种原因，Excel 没有将单元格的内容识别为值。我什至在我的范围内没有任何公式，但它不会将其中一些识别为值。
It will still find it if you change Lookin:=xlValuesto Lookin:=xlFormulas, though, so as a workaround, I did the find with xlValuesfirst, then if the range was still nothing, did the find with xlFormulas. Now it works fine.
但是，如果您更改Lookin:=xlValues为Lookin:=xlFormulas，它仍然会找到它，因此作为一种解决方法，我xlValues首先使用 find 进行了查找，然后如果范围仍然没有，则使用xlFormulas. 现在它工作正常。
