CODEWITHSUNDEEP
excelvba
Saij
Saij

Reputation: 3

How do I use variables in VLOOKUP in excel vba

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.

Upvotes: 0

Views: 3244

Answers (1)

Damian
Damian

Reputation: 5174

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.

Upvotes: 2

Related Questions