G
Gimp
I have an interesting problem. I worked through a vba code that uses 3
variables; the main goal was to find the lowest value on a sheet where
an airline code was entered in a textbox and the weight of a shippment
in another. I had help from folks here in goggle groups as well.
I've learned recently I have to expand my search between two sheets,
not just one. Here is some history:
I have a user form, split into 3 sections, a gateway (origin) section,
a airport (destination) section and a summary (math) section, working
with two sheets. The sheets contain Airport codes and many rates. The
rates are by kilo weights, which can be anything from 0.15 cents to
$3.50 cents, the rates vary.
One sheet (gatewayrates) contains four airport codes, JFK, MIA, LAX and
ORD in column A, column B would contain multiple Airport codes for each
of the four noted above, so for JFK will be listed about 40 times, one
for each of the Airport Codes in B...and so on with MIA...These are
considered our 'gateway' Airports. Other columns in 'gateway'
sheet are the costs per kilo. This would be any dollar value, again
can be anything. Also there is a Minimum value in another column, this
might be something like $50.00, or $30.00...something like that.
On the form I have a Combobox that list the unique values of column B.
When a Origin Airport (or a starting point) is selected, say ATL, the
corresponding gateway code, minimum and per kilo is listed. There are
four textboxes that will show each of the gateways, with their
corresponding minimum and per kilo. So 12 textboxes in all. That
works fine.
Now the other sheet, this is an AirlineData sheet. This is 'like'
the other sheet but has the charges by Destination Airlines (ending
point), minimums and well as per kilo rates. There are columns for
city, airline and 7 columns for price per kilo based on weight. So,
column H, for example are the rates for anything being shipped under 45
kilos, column I is for anything greater than 45 kilos, column J is the
rate for freight at 100 kilos and so on..
The second combobox has unique airport codes for a destination. A
textbox is used to enter the actual weight, say 125 kilos. A button is
used to get the lowest rate with the airport code selected, so for 125
kilos, column I would be used (via a select case statement) as 125 is
closer to 100 than it is to 300, which is column J....make sense. The
other item that would be pulled from this button is the 'gateway'
airline code, JFK, MIA, ORD, LAX...so these gateway airport codes are
in both sheets.
OK, so now on my user form I have my Origin, (ATL), and destination,
Say LHR (Londan). Additionally, say the lowest rate is out of JFK (the
second combobox), there is code to set all the textboxes from the first
part, visible = False, except for the JFK..the matching one. So only
the matching gateway info is displayed after the button is clicked.
On the form there is another the 3rd section (math and summary) that
adds up the minimum from the first two sections as well as the Per
Kilo.....there is other math type textboxes that are there as
well....this too is all calculated with the button.
Here is my next task, Lets use two gateway airport codes as an example,
but really the total would be four. Let say that the first section
(gateway) returns:
Gateway section: JFK per kilo = .50, MIA per kilo = .10
The second section (airline data):
Airport section: JFK per kilo = $1.00, MIA per kilo = $1.10...
In my current situation JFK would be listed as the lowest value from
the airport code, therefore it is used in the 3rd section with all the
math, meaning the overall price per Kilo is $1.50..but as you can see,
the very lowest would be MIA, at $1.20 per kilo.
So, I need the comparison done on both sheets, math is done in code and
then return the lowest on that, not just the airline code sheet.
I know this is a lot of info, I hope it makes sense but I'm trying to
outline what it is I'm doing now and what is working, and what it is
I need. Thanks ....
variables; the main goal was to find the lowest value on a sheet where
an airline code was entered in a textbox and the weight of a shippment
in another. I had help from folks here in goggle groups as well.
I've learned recently I have to expand my search between two sheets,
not just one. Here is some history:
I have a user form, split into 3 sections, a gateway (origin) section,
a airport (destination) section and a summary (math) section, working
with two sheets. The sheets contain Airport codes and many rates. The
rates are by kilo weights, which can be anything from 0.15 cents to
$3.50 cents, the rates vary.
One sheet (gatewayrates) contains four airport codes, JFK, MIA, LAX and
ORD in column A, column B would contain multiple Airport codes for each
of the four noted above, so for JFK will be listed about 40 times, one
for each of the Airport Codes in B...and so on with MIA...These are
considered our 'gateway' Airports. Other columns in 'gateway'
sheet are the costs per kilo. This would be any dollar value, again
can be anything. Also there is a Minimum value in another column, this
might be something like $50.00, or $30.00...something like that.
On the form I have a Combobox that list the unique values of column B.
When a Origin Airport (or a starting point) is selected, say ATL, the
corresponding gateway code, minimum and per kilo is listed. There are
four textboxes that will show each of the gateways, with their
corresponding minimum and per kilo. So 12 textboxes in all. That
works fine.
Now the other sheet, this is an AirlineData sheet. This is 'like'
the other sheet but has the charges by Destination Airlines (ending
point), minimums and well as per kilo rates. There are columns for
city, airline and 7 columns for price per kilo based on weight. So,
column H, for example are the rates for anything being shipped under 45
kilos, column I is for anything greater than 45 kilos, column J is the
rate for freight at 100 kilos and so on..
The second combobox has unique airport codes for a destination. A
textbox is used to enter the actual weight, say 125 kilos. A button is
used to get the lowest rate with the airport code selected, so for 125
kilos, column I would be used (via a select case statement) as 125 is
closer to 100 than it is to 300, which is column J....make sense. The
other item that would be pulled from this button is the 'gateway'
airline code, JFK, MIA, ORD, LAX...so these gateway airport codes are
in both sheets.
OK, so now on my user form I have my Origin, (ATL), and destination,
Say LHR (Londan). Additionally, say the lowest rate is out of JFK (the
second combobox), there is code to set all the textboxes from the first
part, visible = False, except for the JFK..the matching one. So only
the matching gateway info is displayed after the button is clicked.
On the form there is another the 3rd section (math and summary) that
adds up the minimum from the first two sections as well as the Per
Kilo.....there is other math type textboxes that are there as
well....this too is all calculated with the button.
Here is my next task, Lets use two gateway airport codes as an example,
but really the total would be four. Let say that the first section
(gateway) returns:
Gateway section: JFK per kilo = .50, MIA per kilo = .10
The second section (airline data):
Airport section: JFK per kilo = $1.00, MIA per kilo = $1.10...
In my current situation JFK would be listed as the lowest value from
the airport code, therefore it is used in the 3rd section with all the
math, meaning the overall price per Kilo is $1.50..but as you can see,
the very lowest would be MIA, at $1.20 per kilo.
So, I need the comparison done on both sheets, math is done in code and
then return the lowest on that, not just the airline code sheet.
I know this is a lot of info, I hope it makes sense but I'm trying to
outline what it is I'm doing now and what is working, and what it is
I need. Thanks ....