VLOOKUP in VBA Excel VLOOKUP

Question

I am trying to create a VLOOKUP macro that prompts the user to select cells and ranges.

Basically I want to look up a cell value in one tab against a range in another tab and return a column the user selects.

So...This is what I want...

VLOOKUP(""USER SELECT LOOKUP value", "USER SELECT TABLE ARRAY", "USER SELECT COL INDEX NUM", FALSE)

I have this so far but nothing returns when the macro finishes running...

Subject = Application.Input box("select Subject to be looked up")
Sheet = Application.Input box("select tab/range")

          Formula = "=VLOOKUP(""Subject"",""Sheet"",3,False)"

end sub

Scott Craner Scott Craner · Accepted Answer · 2016-07-28T14:31:00

You need to pull the variables out of the quotes and concatenate using &.

Also you want to make them ranges and use their address in the formula:

Dim subject As Range
Dim sheet As Range
Dim clm as long
Set subject = Application.InputBox(prompt:="select Subject to be looked up", Type:=8)
Set sheet = Application.InputBox(prompt:="select tab/range", Type:=8)
clm = Application.InputBox(prompt:="Relative Column Reference", Type:=1)
'Change the range to the cell into which you want the formula placed.
ActiveSheet.Range("A1").Formula = "=VLOOKUP(" & Subject.Address(0,0) & "," & sheet.Address(0, 0, xlA1, 1) & "," & clm & ",False)"

VLOOKUP in VBA Excel VLOOKUP

1 Answers