vba 检查值是否存在于范围内而不循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35714043/
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
Check if value exists in range without looping
提问by click here
I'm used to python syntax where to check if 7is in list1you simply type 7 in list1and it returns a boolean. How can I perform something like this in vba?
我习惯了 python 语法，在那里检查是否7在list1你只是简单地输入7 in list1并返回一个布尔值。如何在 vba 中执行这样的操作？
I'm currently looping through a large range. I want to occasionally check if a value i'm looping over is in a different range. This could get much slower if I had to nest more loops into my loops. What's the fastest way to approach this problem?
我目前正在循环一个很大的范围。我想偶尔检查我循环的值是否在不同的范围内。如果我不得不在循环中嵌套更多循环，这可能会变得更慢。解决这个问题的最快方法是什么？
For i = 400 To 1 Step -1:
'doing other things
'here's some psuedo-code of what I want to do
If Sheets("Sheet2").Cells(i, 2).Value In Sheets("Sheet1").Range("NamedRange")
Sheets("Sheet2").Cells(i, 2).EntireRow.Delete
End If
Next i
回答by Dan Donoghue
Use a countif, if it's greater than zero then you know it exists in the list:
使用 countif，如果它大于零，那么您就知道它存在于列表中：
If Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("NamedRange"), Sheets("Sheet2").Cells(i, 2).Value) > 0 Then
Sheets("Sheet2").Cells(i, 2).EntireRow.Delete
End If
回答by Hari Das
Here is how you can achieve it in just one line
这是您可以在一行中实现它的方法
If Not IsError(Application.Match(ValueToSearchFor, RangeToSearchIn, 0)) Then
// Value found
End If
Example:Search for Blah-Blah in column A of Sheet5
示例：在 Sheet5 的 A 列中搜索 Blah-Blah
If Not IsError(Application.Match("Blah-Blah", Sheets("Sheet5").Range("A:A"), 0)) Then
'The value present in that range
End If
