Need Help with Lookup in visual Basic

Mkammari

Board Regular
Joined
Nov 23, 2005
Messages
65
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]
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

pfarmer

Well-known Member
Joined
Jul 6, 2005
Messages
550
Mkammari said:
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

Board Regular
Joined
Nov 23, 2005
Messages
65
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,120
Messages
5,857,494
Members
431,882
Latest member
saaaaaaaaaaaaaaaaaaaaaa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top