Pass variables into VLOOKUP in VBA

Question

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:

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

Where is the rest of your code? It's pretty impossible to figure out what might be wrong (I can think of 3 or 4 things) without seeing how your variables are declared, etc. — David Zemens
– David Zemens, Commented Jul 18, 2013 at 16:47
@DavidZemens Added; it's nothing complex just an assignment. — JBurace
– JBurace, Commented Jul 18, 2013 at 17:03
See my answer below, and read @DougGlancy's comments for an explanation of why your approach fails when not using a string literal in the function. — David Zemens
– David Zemens, Commented Jul 18, 2013 at 17:52

David Zemens · Accepted Answer · 2013-07-18 17:13:24Z

3

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.

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

answered Jul 18, 2013 at 17:13

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Doug Glancy Over a year ago

FYI, The square brackets are actually more a shortcut for VBA's Evaluate function. The don't work in the OP because i doesn't evaluate to anything within the formula. +1 on your answer.

David Zemens Over a year ago

@DougGlancy I didn't see any i in the OP formula?

Doug Glancy Over a year ago

Oops, that was my simplified testing. I meant the variable CurrName.

David Zemens Over a year ago

@DougGlancy -- why not? I think that must be the root problem in OP's question. There is an assignment statement of currName = "string" in the preceding line. Why would it not evaluate?

Doug Glancy Over a year ago

Because it would be like putting that whole formula into a cell. CurrName makes no sense in that context. There's no evaluation within the Evaluate, in other words it doesn't know that CurrName is a variable that's set to "String". Again, it's just like if you entered that whole formula in a cell. And you are right, that's the root of the OP's problem.

|

Daniel Möller · Accepted Answer · 2013-07-23 11:22:17Z

1

Use the Evaluate function. The formula is a string itself, so you have to concatenate everything into a string:

cellNum = Evaluate("VLOOKUP(""" & currName & """, '2012'!A:M, 13, FALSE)")

edited Jul 23, 2013 at 11:22

answered Jul 18, 2013 at 17:12

Daniel Möller

86.8k24 gold badges202 silver badges222 bronze badges

6 Comments

JBurace Over a year ago

I'm not using the =VLOOKUP() syntax, could you translate that into the way I'm using it?

Daniel Möller Over a year ago

Did you notice the result of what you're trying is "string"?

JBurace Over a year ago

But no cells in my worksheet have formulas, and your code seems like it's trying to insert a formula into a cell. That isn't what I'm doing.

Daniel Möller Over a year ago

Sorry for not undertanding exaclty what you wanted, but now it works. The Evaluate function does the same thing the brackets, but takes a string as parameter, so you can concatenate like done in answer.

JBurace Over a year ago

I'm still getting the #NAME? error when I try this. Are you sure you're not missing a set of quotes?

|

Collectives™ on Stack Overflow

Pass variables into VLOOKUP in VBA

2 Answers 2

11 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related