• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Work allocation via VBA


I am creating Macro to allocate the work to team members. I have created a VBA however there is some issue with the looping.
In the sheet 'MO.Individual Training Tracker' i have stored all the task types, employee name and there training status. Whenever VBA is allocating the tasks it is starting from the first person resulting more tasks are assigning to one person.
What i am expecting is VBA should consider last assigned person and start looping from the next employee.
Please help me on this. your time efforts will be greatly appreciated.



Excel Ninja
Abhishek A
Is somewhere explained - what would need?
Where are tasks to allocate?
Where are employees which should allocate tasks?
Is there ... clear rules?
Thank you for your time..
"MO_AmeritasWorkAllocation" file already has a VBA which is pulling 'task ID', 'Task Type', 'Action Step' & 'Workbasket' from "realtime_sla_role.xls.xlsx" file... Then allocating the tasks to the User ID's (Which is in "MO_AmeritasWorkAllocation" file under "MO.Daily Attendance" sheet)

rule is to allocate the tasks to staff who are trained(TR) on the respective 'task type'. Staff name should appear in Column F (Processor) in MOWorkAllocation sheet.
Could you please run the macro.. After running the Macro, Under 'MO.DailyAttendace' Sheet you will see the staff names in column F. Allocation in column F is not happening properly..


Excel Ninja
Abhishek A
I tried to ask ... but if the best new information is like:
Allocation in column F is not happening properly..
I could figure many same kind of features ...
Hint: You should try to focus eg ... What should happen ... if it would work properly?
... as well as, to even try to modify someone else written code, would need a lot of ... a lot of valid information!
Under the Sheet name “MO.Individual Training Tracker” Column A has task type and Row # 2 has employee names.. And their training status Trained (TR) Not Trained (NT).

Based on the Trained (TR) status, Column F in Sheet “MOWorkAllocation” should update.

I have updated the expected result in Sheet “MOWorkAllocation”

Row #2. Trained (TR) on ADPACPREF task type and no task assigned previously

Row #3. Employee Pramodh URS MS is Trained (TR) on Task Type CONTRACTPO But 1 task is already assigned to him so VBA should look for the next Trained (TR) employee… Who is Shamsher Ahmed



Excel Ninja
Abhishek A
... many .hmm?
... but some comments:

Seems that You already know, without any coding, who should be next? = Who is Shamsher Ahmed
... why/how Your rules is next ... ?

Is Processor (F-col)
... someone who, employee, should do something? ... or from H-col?

I got an image, that You try to get same number of tasks to allocate to possible employees?
... if so, then have You take care that then You did Your code? ... seems skipped?

... Processor Flag ...
For some reason, You seems to want to have there two names ( F & H-columns )... hmm?
Except F17 .. H17 ... hmm?

... H18:H23 ...
There are none expected ... hmm?

Because task types seems to need specific training, You should solve ...
How many employee could do task types?
... to allocate those task types first and
... ... same time take care workload/number of task.
Do You have to take care daily number of tasks per employee would be as equal as possible? ... or weekly/monthly .. or how?
Do all task types have same workload?
... if some task types needs eg 1hr other 8hrs ... then ... eg for daily workload could be different if only take care number of tasks.