Macro for solving the least costly shipping route

[vbaforexcel101]

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):

https://docs.google.com/spreadsheet/ccc?key=0AvqjcsOrtLCRdE5pc1czWG5NM1NCQVljRzlCYkdqeEE&usp=sharingNow

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.

Many thanks in advance for your help. Enjoy!

 

[vbaforexcel101]

bump

 

[mole999]

is this really a macro ?

I ask because

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]

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]

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]

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]

bump