VBA Excel：如何检查值是否在选定的值范围（或当前单元格）中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12435258/
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 Excel: How to check if a value is in a selected range of values (or current cell)
提问by Ken Ingram
I have a reference set of data in a column of cells (it could have been a row, but whatever).
我在一列单元格中有一组参考数据（它可能是一行，但无论如何）。
I want to go through several worksheets, checking a selected range of values and if the current cell value is in the reference data, use it as the value to paste into an Offset of the next series of cells until the current cell has changed to some new value in the reference data, at which time the paste process should repeat for the new value and next series of cells. e.g.
我想查看几个工作表，检查选定的值范围，如果当前单元格值在参考数据中，则将其用作值粘贴到下一系列单元格的偏移量中，直到当前单元格更改为某个值参考数据中的新值，此时应为新值和下一系列单元格重复粘贴过程。例如
Sub doStuff()
Dim RefRange As Range, queryRange As Range, checkCell As Rnage
String pasteDate As String
Set RefRange = Range("A1:A50")
Set queryRange = .Selected
For Each checkCell In queryRange
If checkCell.Value IN RefRange <***here's where I don't know what to use ***>
pasteData = checkCell.Value
' Advance to next item
Next
ElseIf checkCell.Value Not In queryRange <***here's where I don't know what to use ***>
ActiveCell.Offset(0,+1).Value = pasteData
End If
Next
End Sub
I know how to do this in SQL, is there some similar method in VBA?
我知道如何在 SQL 中执行此操作，VBA 中是否有类似的方法？
In SQL:
在 SQL 中：
Select a, b from table where c in ('foo', 'bar', 'baz') OR
Select a, b from table where c in (select e from some_other_table)
TIA
TIA
采纳答案by Reafidy
I doubt there will be a need to loop through all the data simply use a formula in the entire column and replace with the resulting value. Like this:
我怀疑是否需要循环遍历所有数据，只需在整个列中使用公式并替换为结果值。像这样：
Sub Test()
With ActiveSheet.UsedRange.Columns(3).Offset(1)
.Formula = "=IF(ISERROR(MATCH(B2,A:A,0)),"""",B2)"
.Value = .Value
End With
End Sub
The code above looks at all values in the B column to test for a match in the A column. If match is found then the B column value is displayed in the c column. If no match is found it displays a blank cell but you can change that to whatever you want.
上面的代码查看 B 列中的所有值以测试 A 列中的匹配项。如果找到匹配项，则 B 列值显示在 c 列中。如果找不到匹配项，它会显示一个空白单元格，但您可以将其更改为您想要的任何内容。
