# Source Selection based on multiple criteria in a Cycle Time calculation

#### DLAMurph

##### New Member
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 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.

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

Replies
10
Views
1K
Replies
1
Views
350
Replies
6
Views
318
Replies
1
Views
489
Replies
2
Views
405

1,127,832
Messages
5,627,152
Members
416,224
Latest member
RichardHell

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

### Which adblocker are you using?

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