Help with Vlookup - Storing several values on a variable

**Dedes** · 05-12-2011, 01:31 PM

Hi,

I have attached to this post a file explaining my goal:

On cell B5 we enter a % and based on the table I have made, I get a price on cell B8.

This is a simple and classic example on how the Vlookup function works.

What I wanted to know how to do is, how to do all this in VBA. The only thing that would be on the worksheet would be the cell B5 asking for the % - the price value would be recorded on a vairable to be used on a diferent calculation.

So,how do I create on VBA that table? And how I use the vlookup function to search?

I am used to programme in R. In R I would create an object with all my table values. Then I would create my querry asking it to search the value I want on the object containing the table. How is this done in VBA?

thanks
Dedes

**Leith Ross** · 05-12-2011, 01:57 PM

Hello Dedes,

Here is what the VBA macro would look like.

Please Login or Register  to view this content.

**Dedes** · 05-12-2011, 02:55 PM

Originally Posted by Leith Ross

Hello Dedes,

Here is what the VBA macro would look like.

Please Login or Register  to view this content.

Hi Leith

Thanks for the reply.

But to use the code you have put here I need to have the table on the worksheet. Can't I avoid that? I wish to create the table in VBA. Is this possible?

thanks
Dedes

**Leith Ross** · 05-12-2011, 03:07 PM

Hello Dedes,

I am sorry but I am not following you on "VBA creating the table". The table is a range of cells on the worksheet. VBA can not create cells or worksheets. You can only assign object variables to them.

**Dedes** · 05-12-2011, 03:19 PM

Hi

Sorry, i will try to make myself explain better.

I don't want that table to be seen on the worksheet. I wish to do everything in code.

How can I put all that info - the info that is on the table - on my VBA module and run the Vlookup?

thanks
Dedes

**Leith Ross** · 05-12-2011, 03:32 PM

Hello Dedes,

If you want the table hidden then hide columns "D" and "E". The VLOOKUP worksheet will still function. The macro I wrote will also work with the columns hidden.

**Dedes** · 05-12-2011, 04:08 PM

Originally Posted by Leith Ross

Hello Dedes,

If you want the table hidden then hide columns "D" and "E". The VLOOKUP worksheet will still function. The macro I wrote will also work with the columns hidden.

And instead of hiding it, is there a way of making this work without writting the table values on the worksheet?

Dedes

**Leith Ross** · 05-12-2011, 07:58 PM

Hello Dedes,

I am not sure why you don't want to use a worksheet to hold the table data but the only other option is to place the data in a separate file and read the file from VBA. This is neither a very flexible nor direct method to handle the data on a worksheet. Using worksheet functions is a much better choice.

You won't be able to use the VLOOKUP function with the file data because it requires the table to be Range object and not a numeric array. All the searching and comparing will have to handled by VBA macros. If you plan to use other formulas on the sheet, the data could become difficult to manage.