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
<tbody>
</tbody>
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:
<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>
</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.
| Sample matrix | Dest Code #1 | Dest Name #1 | Dest Code #2 | Dest Name #2 | Dest Code #3 | Dest Name #3 |
| Supply Loc #1 | 177.6 | 1/2:33 | 2914.3 | 6/2:08 | 2878.3 | 6/0:01 |
| Supply Loc #2 | 244.9 | 1/4:08 | 1759.4 | 3/16:08 | 1723.4 | 3/3:50 |
| Supply Loc #3 | 988.2 | 2/0:47 | 1003.2 | 2/2:16 | 967.2 | 2/11:31 |
| Supply Loc #4 | 1744.4 | 3/6:07 | 259.9 | 1/13:36 | 223.9 | 1/6:43 |
| Supply Loc #5 | 2899.3 | 6/0:04 | 192.6 | 1/9:06 | 156.6 | 1/22:04 |
<tbody>
</tbody>
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 | 60 | Load/Offload Days: | 3 | ||||||
| To Location | Represented as | ||||||||
| Destination #1 | # of days from start date | ||||||||
| From Location | Distance | dd/hh:mm | Travel Time | Quantity | Required Date | Selected Supply Loc | Load Day | Return Day | Next Delivery |
| Supply Loc #1 | 156.6 | 1/2:04 | 1 | 200 | 5 | Supply Loc #1 | 1.0 | 9.0 | 13.0 |
| Supply Loc #2 | 244.9 | 1/4:08 | 1 | 200 | 14 | Supply Loc #1 | 9.0 | 17.0 | 21.0 |
| Supply Loc #3 | 967.2 | 2/11:31 | 2.5 | 300 | 20 | Supply Loc #2 | 16.0 | 24.0 | 28.0 |
| Supply Loc #4 | 988.2 | 2/12:47 | 2.5 | 200 | 22 | Supply Loc #1 | 18.0 | 26.0 | 30.0 |
| Supply Loc #5 | 1723.4 | 3/3:50 | 3 | 350 | 27 | Supply Loc #3 | 21.5 | 32.5 | 38.0 |
| Supply Loc #6 | 2899.3 | 6/0:04 | 6 | 300 | 33 | Supply Loc #1 | 29.0 | 37.0 | 41.0 |
<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>
</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.
