Need Help with Lookup in visual Basic

[Mkammari]

I'm working on setting up an automatic quote form for a small moving business and am a bit sure how to get the value of a text box dependent on two drop down boxes. Here is the set up.

Here is how it is set up on a worksheet in excel

Column A (Row 2) Ship From
Column B (Row 2) Ship To
Column C Thru H Row 1 are varying weight values.

In the body of the database their is a number of different freight dollars that are dependent on the Ship From, the Ship to, and the weight. For instance, Ship From San Antonio, Ship to Alabama at the various different weights. I am trying to get the freight number to automatically populate when the three choices are made. I'm not sure I can even use vlookup and was wondering what other options there are. I hope this is detailed enough and any help would be greatly appreciated.



[/u]

 

[pfarmer]

I'm working on setting up an automatic quote form for a small moving business and am a bit sure how to get the value of a text box dependent on two drop down boxes. Here is the set up.

Here is how it is set up on a worksheet in excel

Column A (Row 2) Ship From
Column B (Row 2) Ship To
Column C Thru H Row 1 are varying weight values.

In the body of the database their is a number of different freight dollars that are dependent on the Ship From, the Ship to, and the weight. For instance, Ship From San Antonio, Ship to Alabama at the various different weights. I am trying to get the freight number to automatically populate when the three choices are made. I'm not sure I can even use vlookup and was wondering what other options there are. I hope this is detailed enough and any help would be greatly appreciated.


[/u]

Mkammari

Since you are going to have two drop down boxes, this sounds like a solution based on x-y cordinates. Is the layout of the database (is this simply a sheet in the workbook?) based on having City names going down one side and city names going across the top or bottom with a freight charge at the point the two come together.

If so then it sounds like you are interested in picking up the row number of one city and the column number of the other and simply picking up the value of that point. Vlookup/Hlookup would work but you may want to consider using find/match in VBA. This option provides for more flexibility when it comes to where the variables you are keying in on are located.

In Vlookup/Hlookup basically you want that value to be in the first column or row and then you look into an offset based on that column or row. Using find/match you can define the column or row to use as the reference.

Perry

 

[Mkammari]

Thank you for you quick reply. Here are some co-ordinates that may help, and yes the information is on a simple worksheet. I am very familiar with V-lookup and H-lookup, but those only return based on one condition if I'm not mistaken. There are 3 conditions that need to be met in order to return the freight value. I am very unfamiliar with the index and match process

Ship From Location = Range (A11:A148)
Ship To Location = Range (B11:B148)
Weight values = Range (C9:CV)
Freight Values (Answer sought) is (C1:V148)

Hope this helps a bit. I will select three drop downs...I apologize for the earlier misquote. Here is the code in VB I am using

CboShip From
CboShip To
TxtWeight

So dependent on this three inputs, the resulting text box would populate with the freight value. Once again, thank you for the quick reply.