vba 将 vlookup 与文本字符串相结合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13640497/
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
Combining a vlookup with string of text
提问by Chris2015
Can anyone pleaes explain where the syntax is failing for the following formula?
任何人都可以解释以下公式的语法失败的地方吗？
Selection.FormulaLocal = "=concatenate(vlookup(" & i & ", _
inputs!A5:B43,2,false), " some words")""
The formula works fine if I want to simply use the vlookup, but when I try to add text I am coming across syntax errors. Is there a better way to do this without using FormulaLocal. Can I combine a vlookup with text any other way?
如果我只想使用vlookup，该公式可以正常工作，但是当我尝试添加文本时，我遇到了语法错误。有没有更好的方法来做到这一点而不使用FormulaLocal. 我可以以任何其他方式将 vlookup 与文本结合起来吗？
Thanks!
谢谢！
回答by Zev Spitz
The FormulaLocal property (and the Formula property) expect a string (or string literal) which is the same formula you would type from the Formula bar. You're trying to set an invalid string literal:
FormulaLocal 属性（和 Formula 属性）需要一个字符串（或字符串文字），它与您从公式栏中键入的公式相同。您正在尝试设置无效的字符串文字：
"=concatenate(vlookup(" & i & ", inputs!A5:B43,2,false), " some words")""
'< string literal >< VBA >< string literal >< VBA ><string literal>
In order to combine two strings, you need to use &, otherwise it's invalid VBA code:
为了组合两个字符串，你需要使用&，否则它是无效的 VBA 代码：
"=concatenate(vlookup(" & i & ", inputs!A5:B43,2,false), " & some words & ")""
'< string literal >< VBA >< string literal >< VBA ><string>
The string literal is defined with start and end double-quotes. How do you put a double-quote insde a string literal? In VBA you put two double-quotes:
字符串文字是用开始和结束双引号定义的。如何在字符串文字中放置双引号？在 VBA 中，您放置两个双引号：
"=concatenate(vlookup(" & i & ", inputs!A5:B43,2,false), ""some words"")"
'< string literal >< VBA >< string literal >
'Resulting formula (i=5): =concatenate(vlookup(5, inputs!A5:B43,2,false), "some words")
Just bear in mind that this is all on the VBA level. The resulting string also has to be a valid formula for Excel. For example, if iis not a number but a word, the formula will end up looking like this:
请记住，这一切都在 VBA 级别上。结果字符串还必须是 Excel 的有效公式。例如，如果i不是数字而是单词，则公式最终将如下所示：
=concatenate(vlookup(a word, inputs!A5:B43,2,false), "some words")
which is invalid Excel syntax. The solution in this case would be to add double-quotes around i:
这是无效的 Excel 语法。在这种情况下的解决方案是在周围添加双引号i：
"=concatenate(vlookup(""" & i & """, inputs!A5:B43,2,false), ""some words"")"
回答by bonCodigo
Try adding &insted of concat
尝试添加&插入concat
="text" & (vlookupformula)
="text" & (vlookupformula)
so your code will be,
所以你的代码将是，
Selection.FormulaLocal = "= vlookup(" & i & ", _
inputs!A5:B43,2,false) & " some words"
Can you try the following:
您可以尝试以下操作：
Dim strWords = "some words"
Selection.formula = "= vlookup(" & i & ", _
inputs!A5:B43,2,false) "
selection.Formula = .Formula & " & """ & strWords & """"
