在 for 循环中的 VBA 中查找
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19057369/
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
Vlookup in VBA within a for loop
提问by user2824514
I've been having trouble getting this macro to work. I want it to loop through a range and highlight a cell if it does not equal the corresponding value on another sheet through the vlookupfunction. But I keep getting an error with this code:
我一直无法让这个宏工作。如果它不等于另一个工作表上的相应值，我希望它遍历一个范围并突出显示一个单元格vlookup。但是我不断收到此代码的错误：
For Each cell In Worksheets("Sheet1").Range("A2:A1000")
If cell <> Application.WorksheetFunction.VLookup(cell, Worksheets("Sheet2").Range("C3:E128"), 3, 0) Then
cell.Interior.Color = 65535
Else
End If
Next cell
it keeps returning
它不断返回
Run-time error '1004': Unable to get the VLookup property of the WorksheetFunction class
运行时错误“1004”：无法获取 WorksheetFunction 类的 VLookup 属性
any insight is much appreciated!!
任何见解都非常感谢！！
采纳答案by Siddharth Rout
You are getting that error because the VLookup is not able to find and return anything. There are various ways to handle it. Here is one example.
您收到该错误是因为 VLookup 无法找到并返回任何内容。有多种处理方法。这是一个例子。
Sub Sample()
Dim cell As Range
Dim Ret
For Each cell In Worksheets("Sheet1").Range("A2:A1000")
On Error Resume Next
Ret = Application.WorksheetFunction.VLookup(cell, _
Worksheets("Sheet2").Range("C3:E128"), 3, 0)
On Error GoTo 0
If Ret <> "" Then
If cell <> Ret Then
cell.Interior.Color = 65535
End If
Ret = ""
End If
Next
End Sub
回答by Santosh
Try this code
试试这个代码
WorksheetFunction.Vlookupv/s Application.Vlookup
WorksheetFunction.Vlookup与Application.Vlookup
On Error Resume Next
For Each cell In Worksheets("Sheet1").Range("A2:A1000")
Result = Application.VLookup(cell, Worksheets("Sheet2").Range("C3:E128"), 3, 0)
If Result = "Error 2042" Then
'nothing found
ElseIf cell <> Result Then
cell.Interior.Color = 65535
End If
Next
On Error GoTo 0
回答by sam092
Usually "Unable to get the .... property of the WorksheetFunction" arises because there is something wrong with the arguments passed to the function.
通常“无法获得 WorksheetFunction 的 .... 属性”是因为传递给函数的参数有问题。
For example, if any cell within your range contains an error, it will occur
例如，如果您范围内的任何单元格包含错误，则会发生
