VBA to find first available slot

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,365
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 15, 2021
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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.

1623738093140.png


VBA to find first available slot
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,365
Office Version
  1. 365
Platform
  1. Windows
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. (y)
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

New Member
Joined
Jun 15, 2021
Messages
28
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,365
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 15, 2021
Messages
28
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,365
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jun 15, 2021
Messages
28
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
54,365
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,139
Messages
5,835,633
Members
430,372
Latest member
contentment

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