检查 VBA 列中是否存在值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12642164/
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 column in VBA
提问by Trung Tran
I have a column of numbers of over 500 rows. I need to use VBA to check if variable X matches any of the values in the column.
我有一列超过 500 行的数字。我需要使用 VBA 来检查变量 X 是否与列中的任何值匹配。
Can someone please help me?
有人可以帮帮我吗？
采纳答案by Jake Bathman
If you want to do this withoutVBA, you can use a combination of IF, ISERROR, and MATCH.
如果你想这样做没有VBA，你可以使用的组合IF，ISERROR和MATCH。
So if all values are in column A, enter this formula in column B:
因此，如果所有值都在 A 列中，请在 B 列中输入此公式：
=IF(ISERROR(MATCH(12345,A:A,0)),"Not Found","Value found on row " & MATCH(12345,A:A,0))
This will look for the value "12345" (which can also be a cell reference). If the value isn't found, MATCHreturns "#N/A" and ISERRORtries to catch that.
这将查找值“12345”（也可以是单元格引用）。如果未找到该值，则MATCH返回“#N/A”并ISERROR尝试捕获该值。
If you want to use VBA, the quickest way is to use a FOR loop:
如果要使用 VBA，最快的方法是使用 FOR 循环：
Sub FindMatchingValue()
Dim i as Integer, intValueToFind as integer
intValueToFind = 12345
For i = 1 to 500 ' Revise the 500 to include all of your values
If Cells(i,1).Value = intValueToFind then
MsgBox("Found value on row " & i)
Exit Sub
End If
Next i
' This MsgBox will only show if the loop completes with no success
MsgBox("Value not found in the range!")
End Sub
You can use Worksheet Functions in VBA, but they're picky and sometimes throw nonsensical errors. The FORloop is pretty foolproof.
您可以在 VBA 中使用工作表函数，但它们很挑剔，有时会抛出无意义的错误。该FOR循环非常万无一失。
回答by scott
The find method of a range is faster than using a for loop to loop through all the cells manually.
范围的 find 方法比使用 for 循环手动遍历所有单元格要快。
here is an example of using the find method in vba
这是在 vba 中使用 find 方法的示例
Sub Find_First()
Dim FindString As String
Dim Rng As Range
FindString = InputBox("Enter a Search value")
If Trim(FindString) <> "" Then
With Sheets("Sheet1").Range("A:A") 'searches all of column A
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True 'value found
Else
MsgBox "Nothing found" 'value not found
End If
End With
End If
End Sub
回答by chris neilsen
Simplest is to use Match
最简单的是使用 Match
If Not IsError(Application.Match(ValueToSearchFor, RangeToSearchIn, 0)) Then
' String is in range
回答by user11078722
try this:
尝试这个：
If Application.WorksheetFunction.CountIf(RangeToSearchIn, ValueToSearchFor) = 0 Then
Debug.Print "none"
End If
回答by sdanse
Just to modify scott's answer to make it a function:
只是为了修改 scott 的答案以使其成为一个函数：
Function FindFirstInRange(FindString As String, RngIn As Range, Optional UseCase As Boolean = True, Optional UseWhole As Boolean = True) As Variant
Dim LookAtWhat As Integer
If UseWhole Then LookAtWhat = xlWhole Else LookAtWhat = xlPart
With RngIn
Set FindFirstInRange = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=LookAtWhat, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=UseCase)
If FindFirstInRange Is Nothing Then FindFirstInRange = False
End With
End Function
This returns FALSE if the value isn't found, and if it's found, it returns the range.
如果未找到该值，则返回 FALSE，如果找到，则返回范围。
You can optionally tell it to be case-sensitive, and/or to allow partial-word matches.
您可以选择告诉它区分大小写，和/或允许部分单词匹配。
I took out the TRIM because you can add that beforehand if you want to.
我取出了 TRIM，因为如果您愿意，可以事先添加它。
An example:
一个例子：
MsgBox FindFirstInRange(StringToFind, Range("2:2"), TRUE, FALSE).Address
That does a case-sensitive, partial-word search on the 2nd row and displays a box with the address. The following is the same search, but a whole-word search that is not case-sensitive:
这会在第二行进行区分大小写的部分单词搜索，并显示一个包含地址的框。以下是相同的搜索，但不区分大小写的全字搜索：
MsgBox FindFirstInRange(StringToFind, Range("2:2")).Address
You can easily tweak this function to your liking or change it from a Variant to to a boolean, or whatever, to speed it up a little.
您可以根据自己的喜好轻松调整此函数，或将其从 Variant 更改为布尔值或其他任何内容，以稍微加快速度。
Do note that VBA's Find is sometimes slower than other methods like brute-force looping or Match, so don't assume that it's the fastest just because it's native to VBA. It's more complicated and flexible, which also can make it not always as efficient. And it has some funny quirks to look out for, like the "Object variable or with block variable not set" error.
请注意，VBA 的 Find 有时比其他方法（如强力循环或匹配）慢，所以不要仅仅因为它是 VBA 原生的就认为它是最快的。它更加复杂和灵活，这也可能使它并不总是那么高效。它有一些有趣的怪癖需要注意，比如“对象变量或块变量未设置”错误。
回答by Chris
Try adding WorksheetFunction:
尝试添加 WorksheetFunction：
If Not IsError(Application.WorksheetFunction.Match(ValueToSearchFor, RangeToSearchIn, 0)) Then
' String is in range
