VBA to find first available slot

[kapi98]

Hi there.

I was wondering if it'd be possible for Excel to find the next available spot for job that needs to be completed.

All the details and the sample file is included in my original post.

https://www.excelforum.com/excel-programming-vba-macros/1351595-vba-to-find-first-available-slot.html#post5527778

Thanks for your help

 

[Peter_SSs]

Welcome to the MrExcel board!

The link does not work for me but in any case please refer to our Forum Rules and in particular #13 in relation to your post here.

 

[kapi98]

Hi Peter,

Thanks for your message. Yes, I'm cross-posting here as I haven't heard back on the other forum for a week.

I've updated the link below, but generally, I'd like Excel to check if the provided start sate & time are available, and if not, "correct" it to the first available slot. For example, I'd need Excel to change the highlighted start time to 4:00 pm as the previous job is scheduled till 3:45pm.

VBA to find first available slot

 

[Peter_SSs]

Yes, I'm cross-posting here as I haven't heard back on the other forum for a week.

Yes, no problem with that and you had provided a link which is the right thing to do.
The issue is that helpers here should not have to go to another forum to see what your question actually is. Further, if they do go to that forum they actually need to register/log in to be able to see your screen shot or download the file.

Now, to your question ..

1. Do start times only occur on the full hour? eg If that previous job had finished at 3:10 PM, what would be the start time of the highlighted job?
   
   
2. Does anything have to happen with the finish time of that job since its start time is going to be changed?
   
   
3. Can you confirm that the start of the last job shown in your image** is going to have to change by several days?

** Instead of posting images I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

 

[kapi98]

Hi Peter, thanks for these questions.

1. I think it'd usually be every half an hour, so in your example 3.30 pm.

2. There's a formula in-place that calculates the finish time and date.

3. Yes, it'd need to be changed as well.

The problem I see is that the list might not be sorted eventually.

 

[Peter_SSs]

The problem I see is that the list might not be sorted eventually.

Not exactly sure what you mean by that but I was wondering about sorting. For example, in your given sample, what should be the logic of which of those last two items should be next after the first three?

 

[kapi98]

Hi Peter, an operator may want to schedule the forth job before the third one.

 

[Peter_SSs]

Hi Peter, an operator may want to schedule the forth job before the third one.

.. but you are asking us for code to reschedule the jobs and we cannot ask your operator so we need some logic to decide the order.

 

[kapi98]

Generally speaking, the jobs will be automatically added to this worksheet as soon as we have all the details in terms of hours required to complete the job (hrs predict column). However, it doesn't necessarily mean that jobs must be completed in the same order.

I'm really grateful for your help and if the order needs to be sorted by the start date & time, I'll try to make it happen

 

[Peter_SSs]

Test this with a copy of your workbook as it will over-write some of the existing data.

From your image we cannot tell where that data is located (hence my earlier request for XL2BB use) so I have assumed the "START DATE" heading is in cell A1 of the active sheet. If that is not the case, some editing will be required. Post back with details if you need help with that.

This code will re-order (if required) the jobs according to the order of the original start date and time in the table, even though those start date/time values may get pushed back due to overlapping jobs. In the case of the sample data, it will swap the order of the last two rows (as well as adjusting start dates/times for those two rows.

Sub ScheduleJobs()
  Dim SL As Object
  Dim a As Variant, b As Variant
  Dim i As Long, r As Long
  Dim st As Date, prevend As Date
  
  Set SL = CreateObject("System.Collections.Sortedlist")
  With Range("A2", Range("D" & Rows.Count).End(xlUp))
    a = .Value
    ReDim b(1 To 4)
    For i = 1 To UBound(a)
      SL.Add a(i, 1) + a(i, 2) & Format(i, "|0000"), i
    Next i
    For i = 0 To SL.Count - 1
      st = Split(SL.GetKey(i), "|")(0)
      r = SL.GetByIndex(i)
      If i > 0 Then prevend = .Cells(i, 5) + .Cells(i, 6)
      If st < prevend Then st = WorksheetFunction.Ceiling(prevend, 1 / 48)
      b(1) = Int(st)
      b(2) = st - b(1)
      b(3) = a(r, 3)
      b(4) = a(r, 4)
      .Rows(1).Offset(i).Value = b
    Next i
  End With
End Sub