在 VBA 中将变量传递到 VLOOKUP
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17729081/
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
Pass variables into VLOOKUP in VBA
提问by JBurace
I have this code:
我有这个代码：
currName = "string" 'unused
cellNum = [VLOOKUP("string", '2012'!A:M, 13, FALSE)]
But I need to replace "string"with a variable in the VBA code named currName. I tried this:
但是我需要"string"用 VBA 代码中名为currName. 我试过这个：
currName = "string" 'used
cellNum = [VLOOKUP(currName, '2012'!A:M, 13, FALSE)]
What's the appropriate syntax? When I try the 2nd part with using the variable the issue I'm getting is it's returning invalid data (like if I input it back into a cell, it's #NAME?). currNameis simply equal "string". It works fine in the first example without the variable being used.
什么是合适的语法？当我尝试使用变量的第二部分时，我遇到的问题是它返回无效数据（就像我将它输入回单元格一样，它是#NAME?）。currName只是等于“字符串”。在没有使用变量的第一个示例中它工作正常。
回答by David Zemens
I've not seen square brackets used in this manner before. Ordinarily those are shortcut for Rangeobjects. I am able to replicate your error condition.
我以前从未见过以这种方式使用的方括号。通常这些是Range对象的快捷方式。我能够复制您的错误情况。
You can use something like this, instead. Both assignments to cellNumwork without error:
你可以改用这样的东西。两个任务都可以cellNum正常工作：
Sub Test2()
Dim wsFunc As WorksheetFunction: Set wsFunc = Application.WorksheetFunction
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
Dim rngLook As Range: Set rngLook = ws.Range("A:M")
Dim cellNum As Range
Dim currName As String
currName = "string" 'unused
Set cellNum = ActiveCell
cellNum = wsFunc.VLookup("string", rngLook, 2, False)
cellNum = wsFunc.VLookup(currName, rngLook, 2, False)
End Sub
回答by Daniel M?ller
Use the Evaluatefunction. The formula is a string itself, so you have to concatenate everything into a string:
使用该Evaluate功能。公式本身就是一个字符串，因此您必须将所有内容连接成一个字符串：
cellNum = Evaluate("VLOOKUP(""" & currName & """, '2012'!A:M, 13, FALSE)")
