VBA - Prioritize, Generate and Fill Numbers with multiple criterias

cmccall95

New Member
Joined
Nov 11, 2019
Messages
13
This is my first post and was hoping someone could help with my dilemma. I stumbled upon the Solver function in my search but I am not familiar with it and have no idea how to implement it. I will try to be as clear as possible.
My client wants my company to use a particular time sheet. I receive total hours of each employee and must break up the hours(into the "Prep" column) worked per work scope("Line #'s), but there are limits.

  1. Sum of "Actual Applied Hours" to the work scope for each employee must match the employee's worked hours for the day.
  2. Total of "Actual Applied Hours" must equal Total of "Actual Applied Hours" for the Time sheet.(This shouldn't be a problem if the above code works correctly)
  3. A certain amount of hours are given to us per work scope. These are more flexible. We can exceed the Planned Hours, but want to stay below if possible or as low as we can if we do(more often than not) exceed. Therefore I would like to place a priority on the work scopes("Line #'s) that have the most hours remaining first. The code should apply a larger value to these lines.
  4. The crew leader's time should be split up between all lines of his crew using the same rules as above.
    Essentially, I need a code to run in the following manner
    -Find "Actual Applied Hours" out by subtracting delays from "Total Hours" to set the max allowable range

    -Place priority on Lines with the most "Hours remaining". Again, this criteria can be exceeded, but prefer to stay under or as low as possible if exceeded

    -Split each employees "Actual Applied Hours" up between each of their Line #'s according to the priority assigned by the code above. Put this number into the "Prep" column

    -I will then copy and paste the balanced time sheet to a new workbook for reference.

    Can anyone provide any input? I don't even know where to begin with the code. Is this even possible with Excel? This is very time consuming because I have over 200 employees and 30 to 40 Line #'s per time sheet. I must enter this data daily into a progress tracker, fill out time sheets, and report it to my client. The bit of info attached is just an example and can be modified. However, the time sheet cannot be. Any help would be greatly appreciated.


    [TABLE="width: 856"]
    <colgroup><col><col><col><col><col><col><col span="4"><col></colgroup><tbody>[TR]
    [TD][/TD]
    [TD][/TD]
    [TD][/TD]
    [TD][/TD]
    [TD][/TD]
    [TD][/TD]
    [TD][/TD]
    [TD][/TD]
    [TD][/TD]
    [TD][/TD]
    [TD][/TD]
    [/TR]
    [TR]
    [TD][/TD]
    [TD][/TD]
    [TD][/TD]
    [TD][/TD]
    [TD]3[/TD]
    [TD="colspan: 2"]Permit validation[/TD]
    [TD]0.75[/TD]
    [TD]1[/TD]
    [TD][/TD]
    [TD]
    r6FcW.png
    [/TD]
    [/TR]
    </tbody>[/TABLE]

 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

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
Back
Top