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, Jul 18 '13 at 16:47
@DavidZemens Added; it's nothing complex just an assignment. — JBurace, Jul 18 '13 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, Jul 18 '13 at 17:52

score 3 · Accepted Answer · answered Jul 18 '13 at 17:13

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 '13 at 17:13

David Zemens

53,033
11
81
130

1

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. – Doug Glancy Jul 18 '13 at 17:16
1

@DougGlancy I didn't see any `i` in the OP formula? – David Zemens Jul 18 '13 at 17:17
Oops, that was my simplified testing. I meant the variable `CurrName`. – Doug Glancy Jul 18 '13 at 17:25
@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? – David Zemens Jul 18 '13 at 17:28
1

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. – Doug Glancy Jul 18 '13 at 17:45
I might not be fully understanding the conversation, but yes I am not looking for placing a formula in a cell. My code is meant as a calculation in the code and to not touch the spreadsheet. – JBurace Jul 18 '13 at 18:01
My code does not place a formula in the cell. It is just a different means of evaluating your formula in memory. The end result is that the cell will contain the evaluation, not the formula. Cheers. – David Zemens Jul 18 '13 at 18:04
@DavidZemens I'm still not looking to touch the spreadsheet yet though, this is meant to be stored into a VBA variable in the end. The point is that it's finding the location of a specific string for later use by the code, thus why it would need to be in a variable. – JBurace Jul 18 '13 at 18:08
hence why I asked to see more of your code. In my example, `cellNum` is a `Range` object variable. This made sense to me, in the absence of any indication to the contrary, from you. This is why it is helpful to post your *full code*, not just the part you *think* is important. If `cellNum` is a `Variant` or something else, it should still be able to accept this evaluation. Try it. – David Zemens Jul 18 '13 at 18:19
@DavidZemens Due to the sensitive nature of where I work, I sadly cannot just copy-paste my code. I can say that `cellNum` is not used or referenced before the code I've pasted in my original question though if that helps. As long as the code isn't changing any cells (I'm looking for calculations within the code) that works for me. – JBurace Jul 29 '13 at 18:48
What is the typename of `cellNum`? Do you not understand how `wsFunc.VLookup(currName, rngLook, 2, False)` can be used as the right-side of an assignment statement to your `cellNum` variable? – David Zemens Jul 29 '13 at 19:15

Daniel Möller · Answer 2 · 2013-07-23T11:22:17.937

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 '13 at 11:22

answered Jul 18 '13 at 17:12

Daniel Möller

84,878
18
192
214

1

I'm not using the `=VLOOKUP()` syntax, could you translate that into the way I'm using it? – JBurace Jul 18 '13 at 17:14
Did you notice the result of what you're trying is "string"? – Daniel Möller Jul 18 '13 at 17:32
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. – JBurace Jul 18 '13 at 17:48
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. – Daniel Möller Jul 18 '13 at 19:46
I'm still getting the `#NAME?` error when I try this. Are you sure you're not missing a set of quotes? – JBurace Jul 22 '13 at 18:07
Of course, sorry for that. The VLOOKUP takes the string inside quotes.....Answer is updated and working. – Daniel Möller Jul 23 '13 at 11:14

Pass variables into VLOOKUP in VBA

2 Answers2

Linked