将变量传递到VBA中的VLOOKUP中 [英] Pass variables into VLOOKUP in VBA

问题描述

我有此代码:

currName = "string" 'unused
cellNum = [VLOOKUP("string", '2012'!A:M, 13, FALSE)]

但是我需要用VBA代码中名为currName的变量替换"string".我试过了:

But I need to replace "string" with a variable in the VBA code named currName. I tried this:

currName = "string" 'used
cellNum = [VLOOKUP(currName, '2012'!A:M, 13, FALSE)]

什么是合适的语法?当我使用变量尝试第二部分时，我遇到的问题是返回无效数据(例如，如果我将其重新输入到单元格中，则为#NAME?). currName完全等于字符串".在第一个示例中，它可以正常工作，而无需使用变量.

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?). currName is simply equal "string". It works fine in the first example without the variable being used.

推荐答案

我以前从未见过以这种方式使用方括号.通常，这些是Range对象的快捷方式.我能够复制您的错误情况.

I've not seen square brackets used in this manner before. Ordinarily those are shortcut for Range objects. I am able to replicate your error condition.

您可以改用类似的方法. cellNum的两个分配均能正常工作:

You can use something like this, instead. Both assignments to cellNum work without error:

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

这篇关于将变量传递到VBA中的VLOOKUP中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！