Source Selection based on multiple criteria in a Cycle Time calculation


New Member
Jul 11, 2017
I am trying to write some VBA to solve a distribution hub to destination resupply problem. I have over 20 potential destination locations but calculate resupply one location at a time. The destination location can be supported from 30 different supply locations. I built a formula that looks up the closest supply point from a distribution matrix. The matrix has the supply sources on the left side (Single Column) the destination locations as the header across the top (Single Row). The destinations consist of a location code in a cell and name in the adjacent cell, two cells total. The matrix has the distance in miles between the supply and destination in the first column and the travel time in days/hours/min in the second column based on a set travel speed and travel time per day. Sample matrix is as follows

Sample matrixDest Code #1Dest Name #1Dest Code #2Dest Name #2Dest Code #3Dest Name #3
Supply Loc #1177.61/2:332914.36/2:082878.36/0:01
Supply Loc #2244.91/4:081759.43/16:081723.43/3:50
Supply Loc #3988.22/0:471003.22/2:16967.22/11:31
Supply Loc #41744.43/6:07259.91/13:36223.91/6:43
Supply Loc #52899.36/0:04192.61/9:06156.61/22:04


The destination is selected from a user form then published into Cell A3 then the formula determines the 1st closest, 2nd closest, until all supply locations have been racked and stacked closest to farthest in Cells A5 through A34. The destination requires deliveries on set days. The days are represented as the number of days from the start of a contract. An example: the contract starts April 1, 2018 then day 1 represents April 2, 2018 since April one is considered day zero. The quantities and days are provided through user input from a user form. What I need, to determine if a shipment from 1st closest location can travel to, offload, travel back to supply, reload, travel back to destination (the entire process is called Round Robin) for delivery before or on the next required delivery. If not, then a delivery will be scheduled from the 2nd closest location. The next (3rd) required delivery, needs to evaluate shipping from both 1st and 2nd to determine if the 1st closest supply location can make the delivery based on the Round Robin being able to return by the required resupply date, if not, then can the 2nd closest make the delivery based on it’s Round Robin and required resupply date. If it can’t then the 3rd closest supply location will schedule a delivery, etc. The first delivery will always be the closest supply location. I built functions to calculate the different parts of the Round Robin to aid in determining the delivery schedule. Sample sheet is as follows:
Speed 60Load/Offload Days:3
To LocationRepresented as
Destination #1# of days from start date
From LocationDistancedd/hh:mmTravel TimeQuantityRequired DateSelected Supply LocLoad DayReturn DayNext Delivery
Supply Loc #1156.61/2:0412005Supply Loc #
Supply Loc #2244.91/4:08120014Supply Loc #
Supply Loc #3967.22/11:312.530020Supply Loc #
Supply Loc #4988.22/12:472.520022Supply Loc #
Supply Loc #51723.43/3:50335027Supply Loc #321.532.538.0
Supply Loc #62899.36/0:04630033Supply Loc #

<colgroup><col style="mso-width-source:userset;mso-width-alt:4315;width:89pt" width="118"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:4644;width:95pt" width="127"> <col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:5814;width:119pt" width="159"> <col style="mso-width-source:userset;mso-width-alt:4790;width:98pt" width="131"> <col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:2742;width:56pt" width="75"> <col style="mso-width-source:userset;mso-width-alt:3401;width:70pt" width="93"> </colgroup><tbody>

The selected supply location in the above sheet is what I’m trying to develop VBA to calculate. The Load Day, Return Day, and Next Delivery are functions that that I wrote and automatically populate when a selected supply loc is entered. I have tried nested IF statements and CASE statements within a loop but can’t get it to evaluate correctly. My problem is determining if the 1st supply location can be used again once it determines the second needs to fill an order. Also, have the problem once the 3rd supply location is selected it never goes back to check for 1 or 2. Running Excel 2016 on windows 10 systems. Would really appreciate any assistance.

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Watch MrExcel Video

Forum statistics

Latest member

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
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 "".
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