VBA - Prioritize, Generate and Fill Numbers with multiple criterias


New Member
Nov 11, 2019
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.

    3Permit validation0.751

    <colgroup><col><col><col><col><col><col><col span="4"><col></colgroup><tbody>


Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Watch MrExcel Video

Forum statistics

Latest member
Florence Thomas

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...