CODEWITHSUNDEEP
vbaexcel
Chris2015
Chris2015

Reputation: 1050

Combining a vlookup with string of text

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?

Thanks!

Upvotes: 0

Views: 12356

Answers (2)

Zev Spitz
Zev Spitz

Reputation: 15297

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: 

 "=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: 

 "=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: 

 "=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 i is not a number but a word, the formula will end up looking like this:

=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:

"=concatenate(vlookup(""" & i & """, inputs!A5:B43,2,false), ""some words"")"

Upvotes: 1

bonCodigo
bonCodigo

Reputation: 14361

Try adding & insted of concat

="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 & """"

Upvotes: 0

Related Questions