VBA 如果工作表 1 上的单元格与工作表 2 上第一列中的单元格匹配？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26256734/
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 if cell on sheet 1 matches a cell in column I on sheet 2 then?
提问by james smith
I'm trying to use a vba if statement to check if my cell H22 on sheet 1 matches a cell in column I on sheet 2.
我正在尝试使用 vba if 语句来检查工作表 1 上的单元格 H22 是否与工作表 2 上的列 I 中的单元格匹配。
can someone please show me where im going wrong? Thanks
有人可以告诉我我哪里出错了吗？谢谢
If Range("H22").Value Like Worksheets("Sheet 2").Range("I1").Column Then
MsgBox "Match"
Else
MsgBox "No Match Found"
End If
It keeps telling me there is no match found so im guessing it cant find the matching cell value in column I on sheet 2
它一直告诉我没有找到匹配项，所以我猜它在第 2 表的第一列中找不到匹配的单元格值
回答by BradyK
The problem with your code is that you're comparing an individual cell to the entire 'I' column on Sheet 2. What you could do to fix this is use a find to see if a matching value exists. If it does then return match.
您的代码的问题在于您将单个单元格与工作表 2 上的整个“I”列进行比较。您可以做些什么来解决这个问题，那就是使用查找来查看是否存在匹配的值。如果是，则返回匹配项。
Here's some code on how to do this, I would also define Range("H22").Valueso that you know exactly where it is coming from.
这是有关如何执行此操作的一些代码，我还将进行定义，Range("H22").Value以便您确切知道它的来源。
Option Explicit
Sub Macro()
Dim oWs As Worksheet
Dim rSearchRng As Range
Dim lEndNum As Long
Dim vFindVar As Variant
Set oWs = ActiveWorkbook.Worksheets("Sheet2")
lEndNum = oWs.Range("I1").End(xlDown).Row
Set rSearchRng = oWs.Range("I1:I" & CStr(lEndNum))
Set vFindVar = rSearchRng.Find(Range("H22").Value)
If Not vFindVar Is Nothing Then
MsgBox "Match"
Else
MsgBox "No Match Found"
End If
End Sub
结束子
Hereis documentation on the find method.
这是有关 find 方法的文档。
回答by JNevill
Worksheets("Sheet 2").Range("I1").Columnwill return "9" always because Column Iis the 9th column. You are comparing the value in Range("H22")to "9". Unless H22is "9" you will get "No Match Found".
Worksheets("Sheet 2").Range("I1").Column将始终返回“9”，因为 ColumnI是第 9 列。您正在将值Range("H22")与“9”进行比较。除非H22是“9”，否则你会得到“No Match Found”。
Try, instead, using either the .findmethod of the rangeobject or loop through your column looking for values.
相反，请尝试使用对象的.find方法range或循环遍历您的列以查找值。
回答by Gary's Student
Here is a typical example of trying to match a cell value against the values in a column:
以下是尝试将单元格值与列中的值进行匹配的典型示例：
Sub james()
Dim v As Variant, r As Range, rWhere As Range
v = Sheets("Sheet1").Range("H22").Value
Set rWhere = Sheets("Sheet2").Range("I:I")
Set r = rWhere.Find(what:=v, After:=rWhere(1))
If r Is Nothing Then
MsgBox "No match found"
Else
MsgBox "Match found"
End If
End Sub
回答by Gary's Student
If you simply want to check for the existence of the value then bring in a low-level lookup function with Application.Match()or WorksheetFunction.Match()and depend on whether it returns an error. Something like this should suffice.
如果您只是想检查该值是否存在，则使用Application.Match()或引入低级查找函数WorksheetFunction.Match()并取决于它是否返回错误。像这样的东西应该就足够了。
If IsError(Application.Match(Range("H22").Value, Sheets("Sheet 2").Columns("I"), 0)) Then
MsgBox "No Match Found"
Else
MsgBox "Match"
End If
If you wanted to look for more than one then Application.CountIf()would return a number between 0and something higher.
如果您想查找多个，Application.CountIf()则将返回一个介于0和更高值之间的数字。
BTW, there is not usually a space between Sheetand 2.
顺便说一句，Sheet和2之间通常没有空格。
