Excel VBA - 检查单元格是否包含一段文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9416801/
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
Excel VBA - Check cell whether it contains piece of text
提问by CustomX
I want to check a range of cells for a certain piece of text. This text is always in my document, except it's cell is variable (column is always B). So I check a range from 1:75 whether any cells contain a piece of text, but it doesn't seem to work.
我想检查某一段文本的一系列单元格。这个文本总是在我的文档中，除了它的单元格是可变的（列总是 B）。所以我检查了 1:75 的范围内是否有任何单元格包含一段文本，但它似乎不起作用。
Dim FoundRange As Range
Set FoundRange = Cells.Find("5/7 binnen 4h")
Range("I" & EmptyCell + 2).Value = ... (value of cell I on same row as B)
Cell I'm looking for always contains this text Onderhoud 5/7 binnen 4hbut its position can vary, that's why I just need to check whether it contains any of it. When I find that cell, I need the value I on the same row.
我正在寻找的单元格总是包含此文本，Onderhoud 5/7 binnen 4h但它的位置可能会有所不同，这就是为什么我只需要检查它是否包含其中的任何内容。当我找到那个单元格时，我需要同一行的值 I 。
Any suggestions are welcome!
欢迎任何建议！
回答by Steve Homer
Could you not just search for the substring?
你不能只搜索子字符串吗？
Sheet1.Cells.Find("string to find")
Will return the a range containing the string (or nothing if the string can't be found.
将返回包含字符串的范围（如果找不到字符串，则不返回。
For example
例如
Public Sub Macro1()
Dim FoundRange As Range
Set FoundRange = Sheet1.Cells.Find("5/7 binnen 4h")
' display the cell address to the user
MsgBox FoundRange.Address
' put the found value in column i in the same row as the found text in a known location ($C in this case)
Sheet1.Range("$C").Value = Sheet1.Cells(FoundRange.Row, 9).Value
' put the found value in four columns to the right in the same row as the found text in a known location ($C in this case)
Sheet1.Range("$C").Value = FoundRange.Offset(0, 4).Value
End Sub
回答by Tim Williams
This is too much to fit into a comment, so posting it as an answer...
这太多了，无法放入评论中，因此将其发布为答案......
You should be careful when using Find()with just a single argument: if you've previously used Find()in your code and (eg) specified an argument lookat:=xlWholethen you may not get the results you expect, particularly if you're looking for a substring of a cell's value. Settings passed to Find() are persistent: if you don't specify an argument then it may be carried over from the previous use.
Find()仅使用单个参数时应该小心：如果您之前Find()在代码中使用过并且（例如）指定了一个参数，lookat:=xlWhole那么您可能无法获得预期的结果，特别是如果您正在寻找单元格的子字符串价值。传递给 Find() 的设置是持久的：如果您没有指定参数，那么它可能会从以前的使用中延续。
As an example (working with a sheet containing the text "hello tom" in B4:
例如（使用 B4 中包含文本“hello tom”的工作表：
Sub Tester()
Dim f
Set f = ActiveSheet.Cells.Find(what:="tom", lookat:=xlPart)
Report f
Set f = ActiveSheet.Cells.Find(what:="tom", lookat:=xlWhole)
Report f
Set f = ActiveSheet.Cells.Find("tom")
Report f
End Sub
Sub Report(f)
If Not f Is Nothing Then
Debug.Print f.Address
Else
Debug.Print "not found"
End If
End Sub
Running this gives:
运行这个给出：
$B
not found
not found
I seem to recall this is also the case if you've used Find() "manually" and then use it in code later in the same session (didn't test though).
我似乎记得如果您“手动”使用 Find() 然后在同一会话中稍后在代码中使用它（虽然没有测试），情况也是如此。
