Macro for solving the least costly shipping route

vbaforexcel101

New Member
Goodmorning all,

My challenge is the following. Suppose, I have clients who trade goods with each other all over Europe. However some clients prefer the option for the package of their counterpart to be checked by a middleman before the trade goes through. Therefor I would offer 4 possible shipping stations in Belgium, Netherlands, France and Germany.I have found a way to calculate the cost of the shipping route (see C56:N67) based on the middle station (variable cell = D52 and can take values between 1 and 4, the name of the corresponding middle station is in C52):

In a next step, I would like to have a macro who creates a table and solves for the least expensive shipping route, and returns which middle station one should use in order to minimalise the cost of the shipping route.

Last edited:

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

bump

mole999

Well-known Member
is this really a macro ?

If you are shipping direct, then one cost

If you ship via middle man then you have four potential costs

and from those four destinations, an onward second cost

sounds very complex to set up

you will never ship to the middleman, and then ship again if at the same location (I would suggest), and I can't see you would ship to one middle, then onto one of the other three primaries

vbaforexcel101

New Member
Indeed correct but I think it would be most practical to have a macro once I start adding additional shipping stations and destinations.

To keep a long story short. For every individual cell in the range C56:N67, the macro should calculate all 4 different shipping cost solutions by changing the variable in cell D52 (1, 2, 3 and 4). Once he has done this he should remember which of the four inputs of cell D52 resulted in the cheapest shipping route and put the value of cell D52 in the cell where the shipping cost was calculated.

Hope this helps

vbaforexcel101

New Member

you will never ship to the middleman, and then ship again if at the same location (I would suggest), and I can't see you would ship to one middle, then onto one of the other three primaries

This is not necessarily true. E.g. A German ships to a Frenchman through the French middleman. In this case the item will first be shipped to the French shipping station and then from the French shipping station to its final destination in France.

mole999

Well-known Member
France would not be one of the other three primaries, but i take your point

sorry I can't help, I would have done it on graph paper

vbaforexcel101

New Member
bump

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.

1,164,675
Messages
5,838,713
Members
430,566
Latest member
ChanchalSingh

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.

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

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