在 Range.Find VBA 中使用 Or 函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17890004/
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
Using an Or function within Range.Find VBA
提问by user2623878
Is it possible to put and OR function within a range.find? I have this function but I'd like it to look for either of 2 values
是否可以在 range.find 内放置和或函数？我有这个功能，但我希望它寻找 2 个值中的任何一个
With Worksheets("Term").Range("A:A")
Set rng = .Find(What:=Worksheets("Term and Discipline").Range("K7"), _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
Application.Goto rng, True
End With
In the line where you provide what it's looking for I've tried to put in an OR statement but Excel gets mad at me when I try to run it
在您提供要查找的内容的那一行中，我尝试放入 OR 语句，但是当我尝试运行它时，Excel 生我的气
回答by Andy G
I suppose the nearest equivalent would be to perform the searches in parallel (effectively) and use MIN()to select the first cell found.
我想最接近的等效方法是并行（有效地）执行搜索并用于MIN()选择找到的第一个单元格。
Sub FindingNemor()
Dim rngFoo As Range
Dim rngBar As Range
Set rngFoo = Cells.Find(What:="foo", After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
Set rngBar = Cells.Find(What:="bar", After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
If Not rngFoo Is Nothing And Not rngBar Is Nothing Then
Range("A1").Cells(Application.Min(rngFoo.Row, rngBar.Row)).Select
End If
End Sub
It requires extra checks in case only one of rngFoo or rngBar is Nothing.
它需要额外的检查，以防只有 rngFoo 或 rngBar 之一是 Nothing。
AddedChecking for Nothing-ness makes it a little messier:
添加了检查Nothing-ness 使它有点混乱：
Sub FindingNemor()
Dim rngFoo As Range
Dim rngBar As Range
Set rngFoo = Cells.Find(What:="foo", After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
Set rngBar = Cells.Find(What:="bar", After:=Range("A1"), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)
If Not rngFoo Is Nothing And Not rngBar Is Nothing Then
Range("A1").Cells(Application.Min(rngFoo.Row, rngBar.Row)).Select
ElseIf rngFoo Is Nothing Then
If rngBar Is Nothing Then
MsgBox "Neither found."
Else
Range("A1").Cells(rngBar.Row).Select
End If
Else
Range("A1").Cells(rngFoo.Row).Select
End If
End Sub
回答by Joseph
The Range.Findmethod emulates the same window as when you type CTRL+F in Excel. Since you can't search for 2+ values at the same time in that window, I don't believe you can search for 2+ values at the same time through VBA. I think you would have to do the method twice, unfortunately.
该Range.Find方法模拟与在 Excel 中键入 CTRL+F 时相同的窗口。由于您无法在该窗口中同时搜索 2+ 个值，因此我不相信您可以通过 VBA 同时搜索 2+ 个值。不幸的是，我认为您必须执行两次该方法。
If I'm wrong, I would love to know how to do this; it would be so useful. So please feel free to prove me wrong :)
如果我错了，我很想知道如何做到这一点；它会很有用。所以请随时证明我错了:)
From my testing, the Range.Findmethod does not support arrays or a range of more than 1 cell. So those are out.
根据我的测试，该Range.Find方法不支持数组或超过 1 个单元格的范围。所以这些都出来了。
Also, trying to use ORwould not work correctly because ORchecks for TRUE/FALSE and returns TRUE/FALSE.
此外，尝试使用OR将无法正常工作，因为OR检查 TRUE/FALSE 并返回 TRUE/FALSE。
回答by Mr. Mascaro
You need to loop through all the cells in your range and use the Likefunction or InStrin an Ifstatement with Or.
您需要遍历范围内的所有单元格并使用该Like函数或InStr在If语句中使用Or.
