Excel vba 无法获取 Vlookup 属性
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15574632/
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
Excel vba unable to get the Vlookup property
提问by evoandy
I know there are a lot of these questions on here but I can't see what is going wrong.
我知道这里有很多这样的问题，但我看不出哪里出了问题。
I have the following code which checks whether a value is in a column in another workbook.
我有以下代码检查值是否在另一个工作簿的列中。
Dim masterWbk As Workbook
Dim oWbk As Workbook
Dim RowCount As Integer
Dim LookupRange As Range
Dim Exists As Variant
Dim a As Integer
Dim i As Integer
Dim jobnumber As String
RowCount = WorksheetFunction.CountA(Sheets("Sheet1").Range("A1").EntireColumn)
masterWbk.Activate
Set LookupRange = masterWbk.Sheets("Sheet1").Range("C1:C100")
a = 0
For i = 0 To RowCount - 1
jobnumber = oWbk.Sheets("Sheet1").Range("A2").Offset(i, 0).Value
' On Error GoTo ExistsError:
Exists = Application.WorksheetFunction.VLookup(jobnumber, LookupRange, 1, False)
Now the value is definitely in the the lookup range and is formatted the same but the Vlookup just won't work. It works fine as an excel formula.
现在该值肯定在查找范围内并且格式相同，但 Vlookup 将不起作用。它作为excel公式工作正常。
What have I missed?
我错过了什么？
回答by scott
Using find it'd look like this-
使用 find 它看起来像这样 -
Dim masterWbk As Workbook
Dim oWbk As Workbook
Dim RowCount As Integer
Dim LookupRange As Range
Dim Exists As Variant
Dim a As Integer
Dim i As Integer
Dim jobnumber As String
RowCount = WorksheetFunction.CountA(Sheets("Sheet1").Range("A1").EntireColumn)
masterWbk.Activate
Set LookupRange = masterWbk.Sheets("Sheet1").Range("C1:C100")
a = 0
For i = 0 To RowCount - 1
jobnumber = oWbk.Sheets("Sheet1").Range("A2").Offset(i, 0).Value
If Trim(jobnumer) <> "" Then
With lookuprange
Set Rng = .Find(What:=jobnumber, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Exists = 1
Else
Exist = 0
End If
End With
End If
回答by grahamj42
I think there is a problem with the documentation of WorksheetFunction.Vlookup. In my tests on Excel 2007, if the search string is found, the value of the corresponding cell is returned. If you search for an exact match, you get the dreaded error 1004.
我认为WorksheetFunction.Vlookup. 在我对 Excel 2007 的测试中，如果找到搜索字符串，则返回相应单元格的值。如果您搜索完全匹配，则会收到可怕的错误 1004。
If you want to use Vlookup,
如果你想使用 Vlookup，
...
Dim FoundText as String
...
FoundText=""
On Error Resume Next
FoundText=Application.WorksheetFunction.VLookup(jobnumber, LookupRange, 1, False)
On Error Goto 0
Exists= (FoundText <> "")
...
回答by David Zemens
This seems to work fine in Excel 2010...
这似乎在 Excel 2010 中工作正常......
I would suggest you're likely getting an error with VLOOKUP because you've dimensioned jobNumberas String and despite the worksheet/cell "formatting", the job numbers in your worksheets are likely stored as numeric values.
我建议您可能会遇到 VLOOKUP 错误，因为您已将尺寸标注jobNumber为字符串，尽管工作表/单元格“格式化”，但工作表中的作业编号可能存储为数值。
So, the problem is that "123" <> 123. Test whether jobNumber is numeric, and then make sure you're passing the right sort of data to the function.
所以，问题是 "123" <> 123。测试 jobNumber 是否是数字，然后确保将正确的数据类型传递给函数。
If IsNumeric(jobNumber) Then
Exists = Application.WorksheetFunction.VLookup(CLng(jobNumber), lookupRange, 1, False)
Else:
Exists = Application.WorksheetFunction.VLookup(jobNumber, lookupRange, 1, False)
End If
