VBA Excel：如何检查值是否在选定的值范围内（或当前单元格） [英] VBA Excel: How to check if a value is in a selected range of values (or current cell)

问题描述

我在单元格列中有一组参考数据（它可能是一行，但无论如何）。

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

我知道如何在SQL中执行此操作，VBA中是否有类似的方法？

I know how to do this in SQL, is there some similar method in VBA?

在SQL中： p>

In 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

推荐答案

我怀疑将需要循环遍历所有的数据，只需在整个列中使用一个公式，并用结果值替换。像这样：

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

上面的代码查看了B中的所有值列以测试A列中的匹配。如果找到匹配项，则B列值显示在c列中。如果没有找到匹配，它会显示一个空白单元格，但是您可以将其更改为任何您想要的。

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.

这篇关于VBA Excel：如何检查值是否在选定的值范围内（或当前单元格）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！