Excel VBA：错误 1004 WorkSheetFunction“无法获取 Vlookup 属性”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22809515/
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: Error 1004 WorkSheetFunction 'Unable to get Vlookup property"
提问by EmilioSandoz
Trying to write a quick piece of VBA in Excel 2010 to
试图在 Excel 2010 中编写一个快速的 VBA
- Use Vlookup to find a value
- Return the value in the 3rd Column
- Set a given cell to this value
- 使用 Vlookup 查找值
- 返回第三列中的值
- 将给定单元格设置为此值
My difficulty is with the formula.
我的困难在于公式。
Sub Metrics123()
Dim x As Integer
x = Application.WorksheetFunction.VLookup("Test", "A7:D9", 3, False)
Range("A1").Value = x
End Sub
When I run this I hit the error 1004: 'Unable to get the Vlookup Property of the WorksheetFunction
当我运行这个时，我击中了 error 1004: 'Unable to get the Vlookup Property of the WorksheetFunction
Any pointers appreciated!
任何指针表示赞赏！
采纳答案by Dmitry Pavliv
Two ways for you.
两种方式给你。
1)Use .Formulaproperty:
1)使用.Formula属性：
With ThisWorkbook.Worksheets("Sheet1").Range("A1")
.Formula = "=VLOOKUP(""Justin"",A7:D9,3,FALSE)"
.Value = .Value
End With
where .Value = .Valuerewrites formula with it's result
其中.Value = .Value重写公式与它的结果
2)use Application.VLookupwith Range("A7:D9")instead "A7:D9":
2)使用Application.VLookupwithRange("A7:D9")代替"A7:D9"：
Dim x
With ThisWorkbook.Worksheets("Sheet1")
x = Application.VLookup("Justin", .Range("A7:D9"), 3, False)
Range("A1").Value = x
End With
Note, that xshould be Variant, because if nothing found, Application.VLookupreturns Error 2042(#N/A)
请注意，这x应该是Variant，因为如果没有找到，则Application.VLookup返回Error 2042( #N/A)
