Need Help with VBA coding random Training slots

**KashiefD** · 03-03-2024, 02:32 PM

Hi guys

I need some help with my VBA code please. The current code I have is assigning training on one day and for some its outside of there work shift.

So here is the information I am working with.

Sheet TRS
1.1 Range B2:B20000 contains various logins each login will appear multiple times each row representing one shift of there's for a week. We need to check this range and look for the logins who need training. The logins who need training can be identified in 2.1
1.2Range R2:R2000 we find the start of the shift for each login found in 1.1 . remember each login will be found in multiple rows in the range in 1.1 similarly there start times will be found multiple times. The format here is YYYY-MM-DD HH:MM . Use this information only to determine the day of the week they are working and what time there shift ends. The date found here is nothing to do with the date during which we must schedule training this can be found in 2.3
1.3 Range S2:S2000 we find the end of the shift for each login found in 1.1 . remember each login will be found in multiple rows in the range in 1.1 similarly there end times will be found multiple times. The format here is YYYY-MM-DD HH:MM . Use this information only to determine the day of the week there shift ends on and what time there shift ends. The date found here is nothing to do with the date during which we must schedule training this can be found in 2.3

Sheet Training Input
2.1 Range A2:A250 this is the list of logins for who Training must be assigned
2.2 Cell H5 this is the duration for each training slots the amount found in this cell is the amount of minutes for which training must be assigned.
2.3 Cells H6 and I6 these cells are the period during which the training must be assigned. H6 is the first date during which training can be assigned and I6 is the last date during which training can be assigned.

Sheet Training Output
3.1 Range A2:A250 Here we find the same login we found in 2.1 and the row where we find the login is the row in which we must enter the training slot date and time.
3.2 Range E2:e250 this is the range where start of the training slots must be inputted remember it must be inputted in the same row as where that login was found in 3.1 . The format for the slot that must be inputted is YYYY-MM-DD HH:MM
3.3 Range F2:F250 this is the range where end of the training slots must be inputted remember it must be inputted in the same row as where that login was found in 3.1 . The format for the slot that must be inputted is YYYY-MM-DD HH:MM. Also remember the time between 3.3 and 3.2 must be equal in minutes to the amount found in 2.2

Steps thats needed
-Identify the logins needing training (2.1)
-Check Sheet TRS to see which rows in A is blank that way only search for logins shift times in rows which contain data
-Find out what days of the week (Plural) the logins identified works (1.1 + 1.2 + 1.3)
-Identify the duration for the training slot (2.2)
-Find days during the training period (2.3) that the logins will be working.
-randomly assign training within a day and time that each login works and plot the data on the Training Output sheet (3.2 + 3.3)

Things to remember
-1.1 has multiple instances of each login
-1.2 + 1.3 the date here is only used to get the days of the week each login works.
-Spread out the training through out the training period 2.3
-No body must be training at the same time.

Below is the code I have gotten but its kicking out training slots but all training its giving is on the same day and some logins dont work on that day..

Please Login or Register  to view this content.

**alansidman** · 03-03-2024, 05:49 PM

Administrative Note:

We would very much like to help you with your query, however your post does not comply with our guidelines

Please take a moment to review how to add code tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Guideline #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here.

I have added the code tags for you today. Please take a few minutes to re-read our guidelines and rules that you agreed to when you joined and comply in the future.