Perform vlookup using prompt boxes

**The_Cat** · 03-05-2010, 08:37 AM

I don't know whether this is possible or not, but what i need is to perforn a macro to run a vlookup using prompt boxes.
I have a list of exchange rates that change on a monthly basis, so i need two prompts, one to enter the lookup value (currency code - GBP EUR USD etc.) and another to enter the month in order to return the correct column from the table.
Sorry if this doesn't make sense, but in essence i need the macro to perform this below
vlookup(prompt1 - currency ,ex rate table,prompt 2 - month ,false)

Thanks

The Cat

**blane245** · 03-05-2010, 10:31 AM

Welcome to the boards!

If you could give an excel example of the inputs and outputs, that would help explain things a bit better.

**The_Cat** · 03-05-2010, 01:07 PM

Thanks Blane. I think i have solved it. See below where it returns NUM1 as vlookup value and NUM2 as the column reference

ActiveCell.Select
ActiveCell.Offset(0, 1).Activate
NUM1 = Application.InputBox("Enter Currency Code.", "Three letter code", Type:=2)
ActiveCell.Formula = NUM1
ActiveCell.Offset(0, 1).Activate
NUM2 = Application.InputBox("Enter Month.", "Month", Type:=1)
ActiveCell.Formula = NUM2
ActiveCell.Offset(0, -2).Activate
ActiveCell.Formula = "=+VLOOKUP(RC[1],'[LH ex rates.xls]Sheet1'!C3:C18,RC[2]+1,false)"