Source Selection based on multiple criteria in a Cycle Time calculation

[DLAMurph]

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

[TABLE="width: 739"]
<tbody>[TR]
[TD]Sample matrix[/TD]
[TD]Dest Code #1[/TD]
[TD]Dest Name #1[/TD]
[TD]Dest Code #2[/TD]
[TD]Dest Name #2[/TD]
[TD]Dest Code #3[/TD]
[TD]Dest Name #3[/TD]
[/TR]
[TR]
[TD]Supply Loc #1[/TD]
[TD]177.6[/TD]
[TD]1/2:33[/TD]
[TD]2914.3[/TD]
[TD]6/2:08[/TD]
[TD]2878.3[/TD]
[TD]6/0:01
[/TD]
[/TR]
[TR]
[TD]Supply Loc #2[/TD]
[TD]244.9[/TD]
[TD]1/4:08[/TD]
[TD]1759.4[/TD]
[TD]3/16:08[/TD]
[TD]1723.4[/TD]
[TD]3/3:50[/TD]
[/TR]
[TR]
[TD]Supply Loc #3[/TD]
[TD]988.2[/TD]
[TD]2/0:47[/TD]
[TD]1003.2[/TD]
[TD]2/2:16[/TD]
[TD]967.2[/TD]
[TD]2/11:31[/TD]
[/TR]
[TR]
[TD]Supply Loc #4[/TD]
[TD]1744.4[/TD]
[TD]3/6:07[/TD]
[TD]259.9[/TD]
[TD]1/13:36[/TD]
[TD]223.9[/TD]
[TD]1/6:43[/TD]
[/TR]
[TR]
[TD]Supply Loc #5[/TD]
[TD]2899.3[/TD]
[TD]6/0:04[/TD]
[TD]192.6[/TD]
[TD]1/9:06[/TD]
[TD]156.6[/TD]
[TD]1/22:04[/TD]
[/TR]
</tbody>[/TABLE]

The destination is selected from a user form then published into Cell A3 then the formula determines the 1^st closest, 2^nd 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 1^st 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 2^nd closest location. The next (3^rd) required delivery, needs to evaluate shipping from both 1^st and 2^nd to determine if the 1^st 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 2^nd closest make the delivery based on it’s Round Robin and required resupply date. If it can’t then the 3^rd 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:

	Represented as
	# of days from start date
dd/hh:mm
1/2:04
1/4:08
2/11:31
2/12:47
3/3:50
6/0:04

<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>
[TD="class: xl63, width: 118"]Speed [/TD]
[TD="class: xl65, width: 64"]60[/TD]
[TD="width: 127"]Load/Offload Days:[/TD]
[TD="class: xl66, width: 79"]3[/TD]
[TD="width: 64"][/TD]
[TD="width: 159"][/TD]
[TD="width: 131"][/TD]
[TD="width: 64"][/TD]
[TD="width: 75"][/TD]
[TD="width: 93"][/TD]

[TD="class: xl69, width: 388, colspan: 4"]To Location[/TD]

[TD="class: xl64"][/TD]

[TD="class: xl63, width: 118"]Destination #1[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 127"][/TD]
[TD="class: xl66"][/TD]

[TD="class: xl64"][/TD]

[TD="class: xl63, width: 118"]From Location[/TD]
[TD="class: xl63, width: 64"]Distance[/TD]

[TD="class: xl66"]Travel Time[/TD]
[TD="class: xl66"]Quantity[/TD]
[TD="class: xl66"]Required Date[/TD]
[TD="class: xl66"]Selected Supply Loc[/TD]
[TD="class: xl66"]Load Day[/TD]
[TD="class: xl64"]Return Day[/TD]
[TD="class: xl66"]Next Delivery[/TD]

[TD="class: xl67"]Supply Loc #1[/TD]
[TD="class: xl63, width: 64, align: right"]156.6[/TD]

[TD="class: xl66"]1[/TD]
[TD="class: xl66"]200[/TD]
[TD="class: xl66"]5[/TD]
[TD="class: xl67"]Supply Loc #1[/TD]
[TD="class: xl68"]1.0[/TD]
[TD="class: xl68"]9.0[/TD]
[TD="class: xl68"]13.0[/TD]

[TD="class: xl67"]Supply Loc #2[/TD]
[TD="align: right"]244.9[/TD]

[TD="class: xl66"]1[/TD]
[TD="class: xl66"]200[/TD]
[TD="class: xl66"]14[/TD]
[TD="class: xl67"]Supply Loc #1[/TD]
[TD="class: xl68"]9.0[/TD]
[TD="class: xl68"]17.0[/TD]
[TD="class: xl68"]21.0[/TD]

[TD="class: xl67"]Supply Loc #3[/TD]
[TD="align: right"]967.2[/TD]

[TD="class: xl66"]2.5[/TD]
[TD="class: xl66"]300[/TD]
[TD="class: xl66"]20[/TD]
[TD="class: xl67"]Supply Loc #2[/TD]
[TD="class: xl68"]16.0[/TD]
[TD="class: xl68"]24.0[/TD]
[TD="class: xl68"]28.0[/TD]

[TD="class: xl67"]Supply Loc #4[/TD]
[TD="class: xl63, width: 64, align: right"]988.2[/TD]

[TD="class: xl66"]2.5[/TD]
[TD="class: xl66"]200[/TD]
[TD="class: xl66"]22[/TD]
[TD="class: xl67"]Supply Loc #1[/TD]
[TD="class: xl68"]18.0[/TD]
[TD="class: xl68"]26.0[/TD]
[TD="class: xl68"]30.0[/TD]

[TD="class: xl67"]Supply Loc #5[/TD]
[TD="class: xl63, width: 64, align: right"]1723.4[/TD]

[TD="class: xl66"]3[/TD]
[TD="class: xl66"]350[/TD]
[TD="class: xl66"]27[/TD]
[TD="class: xl67"]Supply Loc #3[/TD]
[TD="class: xl68"]21.5[/TD]
[TD="class: xl68"]32.5[/TD]
[TD="class: xl68"]38.0[/TD]

[TD="class: xl67"]Supply Loc #6[/TD]
[TD="class: xl63, width: 64, align: right"]2899.3[/TD]

[TD="class: xl66"]6[/TD]
[TD="class: xl66"]300[/TD]
[TD="class: xl66"]33[/TD]
[TD="class: xl67"]Supply Loc #1[/TD]
[TD="class: xl68"]29.0[/TD]
[TD="class: xl68"]37.0[/TD]
[TD="class: xl68"]41.0[/TD]

</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 1^st supply location can be used again once it determines the second needs to fill an order. Also, have the problem once the 3^rd 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.