Adding a variable in a VLookUp

Question

I'm having a problem with a vlookup function. First code shows that vlookup know what is "TempArg'17", but now I want to change it this text to string and write it inside vlookup, so I created a string temp.

Dim temp As String
Dim num As Int
num = 17

temp = "TempArg" + "'" + Cstr(num)

Here is the old code that works:

ActiveCell.FormulaR1C1 = _
        "=VLOOKUP([@[" & Chr(10) & "Številka]],'TempArg''17'!C[-16]:C[-17],2,FALSE)"

And here is the code that I want to change and that doesn't work:

ActiveCell.FormulaR1C1 = _
        "=VLOOKUP([@[" & Chr(10) & "Številka]],temp'!C[-16]:C[-17],2,FALSE)"

Thanks in advance.

Whare the two column in the lookup range?

user4039065
– user4039065

2018-03-14 15:32:52 +00:00
Commented Mar 14, 2018 at 15:32 — user4039065
– user4039065, Commented Mar 14, 2018 at 15:32

Maddy · Accepted Answer · 2018-03-14 16:14:56Z

2

Try this

"=VLOOKUP([@[" & Chr(10) & "Številka]],'" & temp & "'!C[-16]:C[-17],2,FALSE)"

edited Mar 14, 2018 at 16:14

answered Mar 14, 2018 at 15:32

Maddy

7716 silver badges14 bronze badges

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

2 Comments

user4039065 Over a year ago

might need a ' on the left side of temp.

gringo1011 Over a year ago

Thanks, but it says "Syntax Error" :-/

Collectives™ on Stack Overflow

Adding a variable in a VLookUp

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related