How do I use variables in VLOOKUP in excel vba

Question

I need to use a vlookup function where I would get all the arguments from different functions and use them here.

Eg: ActiveCell.Formula= “=VLOOKUP(B2, ‘sheet 2’!$A:$I, 4, FALSE)

I would like to write this as:

VLOOKUP(element, f_range, col_num, true_false)

Currently, I only want the column to be dynamic, but in the future, I would require all these to be dynamic.

When you write a formula you do it between "" because its a literal string which will be outputed to an excel cell. To use variables you need to write them out of the quotes. i.e. "=VLOOKUP(" & element & "," & f_range & "," & col_num & "," true_false & ")" don't forget the commas because they are aswell on the formula. & is a concatenate operator, it will put together everything you are joining with it. — Damian
– Damian, Commented Sep 13, 2019 at 10:55

Damian · Accepted Answer · 2019-09-13 11:03:49Z

2

When you write a formula you do it between "" because its a literal string which will be outputed to an excel cell. To use variables you need to write them out of the quotes.

i.e. "=VLOOKUP(" & element & "," & f_range & "," & col_num & "," true_false & ")"

Don't forget the commas because they are aswell on the formula. & is a concatenate operator, it will put together everything you are joining with it.

There is another way if you are willing to have values instead the formulas on your cells:

ActiveCell.Value = Application.VLOOKUP(element, f_range, col_num, true_false)

This will calculate the value and put it to your cell. No need to concatenate anything, just give it the parameters as variables.

answered Sep 13, 2019 at 11:03

Damian

5,1741 gold badge12 silver badges22 bronze badges

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

2 Comments

Saij Over a year ago

Thank you for your comment, both of them workded well. I would like to use the second method, but the first one, even though it seems trickier is better for my project.

Damian Over a year ago

@Saij glad to help. Consider marking the answer as correct so others may find it easier :)

Collectives™ on Stack Overflow

How do I use variables in VLOOKUP in excel vba

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