在VLOOKUP函数中添加变量的问题求助

Hey there! Let's troubleshoot why your VLOOKUP formula breaks when swapping the hardcoded "TempArg'17" with the string variable temp. The root issue almost always comes down to properly wrapping your variable in Excel's required text quotes when building the formula in VBA.

Why the Old Code Worked #

Your working hardcoded version likely looked something like this:

' Example working old code
Range("D1").Formula = "=VLOOKUP(""TempArg'17"", A:C, 3, FALSE)"

In VBA, the double double-quotes ("") get converted to a single double-quote in the final Excel formula, resulting in:

=VLOOKUP("TempArg'17", A:C, 3, FALSE)
Excel recognizes this as a valid text string to search for, so VLOOKUP runs smoothly.

What's Wrong with the New Code #

If your new code looks like this (a common mistake):

' Example broken new code
Dim temp As String
temp = "TempArg'17"
Range("D1").Formula = "=VLOOKUP(" & temp & ", A:C, 3, FALSE)"

The resulting Excel formula will be:

=VLOOKUP(TempArg'17, A:C, 3, FALSE)
Excel sees TempArg'17 as a named range (not a text string), so it can't find the value you're searching for—hence the error.

The Fix: Properly Wrap the Variable in Quotes #

You need to add Excel's required double-quotes around the temp variable when building the formula. There are two clean ways to do this in VBA:

Option 1: Use Escaped Double-Quotes ("")

Dim temp As String
temp = "TempArg'17"
' Wrap temp in escaped double-quotes
Range("D1").Formula = "=VLOOKUP(""" & temp & """, A:C, 3, FALSE)"

This will generate the exact same valid formula as your original hardcoded version.

Option 2: Use Chr(34) (More Readable)

Chr(34) is the VBA equivalent of a double-quote character. This can make longer formulas easier to read:

Dim temp As String
temp = "TempArg'17"
Range("D1").Formula = "=VLOOKUP(" & Chr(34) & temp & Chr(34) & ", A:C, 3, FALSE)"

Quick Check #

After running the fixed code, check the formula bar in Excel—you should see the temp value wrapped in double-quotes, just like your original working formula. That's the key to making VLOOKUP recognize it as a text string.

内容的提问来源于stack exchange，提问作者gringo1011