• 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

    Hui...

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

VBA Logic to loop through a set of tasks and assign employees

Status
Not open for further replies.
R

ravikiran

Member
Hi All,

I have an issue developing a VBA program for a small logic. I tried posting it earlier here with no luck. Seems like I didn't communicate my requirement properly. So I am re-writing my requirements:

I need help with a small VBA logic that can loop through a set of tasks and assign Employees to the tasks.

Process:
  1. I have a set of tasks (Task 1 to Task 10)
  2. I have 3 Employees (John, Ken and Kyle). Everyday I allocate different % of tasks for each employee based on their work load. For e.g
    • John - 70% of tasks
    • Ken - 15% of tasks
    • Kyle - 15% of tasks
  3. I need the macro to loop these tasks and assign John, Ken or Kyle.
Final output will be similar to this:
Task 1John
Task 2John
Task 3John
Task 4John
Task 5John
Task 6John
Task 7John
Task 8Ken
Task 9Ken
Task 10Kyle

The challenge I have is that the % of allocation changes everyday and occasional an additional employee(s) added.

I would be grateful to anyone who can help me with this logic.

Thanks in advance,

cheers,
Ravi.

PS: Moderators, please excuse me for this second post. I thought it would be a better idea to re-post when I change the requirements completely. Thank you

EDITED (SirJB7)

PS2: Original thread: http://chandoo.org/forum/threads/automatic-task-allocation-to-users.13490
 
Last edited by a moderator:
Hi Ravi,

Please see the code below for your issue, and also i have attached the workbook for your reference.

Please let me know if you need further assistance.

Code:

Sub EmpTask()

Dim i As Integer
Dim perc As Integer

Sheets("sheet2").Select
Range("a2").Select
Selection.End(xlDown).Select
cnt = ActiveCell.Row - 1

Sheets("sheet1").Select

If Cells(1, "f").Value = 0.01 Then
i = 2
k = 2
perc = 0
Do While Cells(i, 1).Value <> ""
emp = Cells(i, 1).Value
perc = (Cells(i, 2).Value * cnt) + perc

For j = k To perc + 1
Sheets("sheet2").Select
Cells(k, 2).Value = emp
k = k + 1
Next j
i = i + 1
Sheets("sheet1").Select
Loop

Else
MsgBox "Please assign the task properly"
Exit Sub
End If

MsgBox "Task Completed...look in sheet2"

End Sub



Regards
Abdul Matheen
 

Attachments

  • Employee Task.xlsm
    17.1 KB · Views: 224
Thanks Abdul. This is exactly what I am after.

Made some changes to the code as some scenarios are not working. E.g if we have 21 tasks instead of 20, the last one is left without being assigned. I modified the code and it's working fine.

Is there a chance to have generate a report at the end of the process?
e.g:
Calculated 4.2 6.3 4.2 6.3
Actual 4 6 4 7

This helps me to change the one additional task that falls into the last employee's bucket. I can do it manually.

Thank you very much for your help.

cheers,
Ravi.
 
Hi Ravi,

i have checked at my end but it is showing me the correct assignment if it 21 also. check once again i have attaching the workbook again.

Regards
Abdul Matheen
 

Attachments

  • Employee Task.xlsm
    17.9 KB · Views: 283
Yes Abdul, the logic works fine in this attachment. Can you please download and check the last file you sent me?
There are 20 tasks. Add Task21 to it and then try running the allocation macro without changing the percentage.

cheers,
Ravi.
 
I have downloaded and checked it have 21 tasks and working fine .... ok however you got the logic right....Really interesting task..good. If you have any similar task post me... Thanks..

Regards
Abdul Matheen
 
ravikiran said:
PS: Moderators, please excuse me for this second post. I thought it would be a better idea to re-post when I change the requirements completely. Thank you
Click to expand...
Hi, ravikiran!
As a matter of fact that's what you should do when you change the requirements completely. But in this case it's just a method change, it's still related to the same problem but with a different logic. This should have continued at the original thread, or you should have posted a link to it in the 1st comment (fixed).
Regards!
 
Ravi,

i was going to help you with the macro, but you seemed to take your own path, I can only help when I have the time.
Glad you got the answer
 
@SirJB7,

Thank you JB7. I should have included the link. It slipped my mind. Thanks for the edit anyway.

@kchiba,
Your formula worked great. I saw a better approach with the macro, as this is supposed to be used by Excel beginners. Click of a button and everything working fine. Thanks for the initiation.

@Abdul
I finished the fine tuning and integrated it into the application. Everything is working fine. Thanks to you! You have been of a great help!

cheers,
Ravi.
 
Abdul Matheen said:
Hi Ravi,

i have checked at my end but it is showing me the correct assignment if it 21 also. check once again i have attaching the workbook again.

Regards
Abdul Matheen
Click to expand...
Hi Ravi,

Yes i Checked,i Added Task 21 but it not assigned for 21.
ravikiran said:
Thanks Abdul. This is exactly what I am after.

Made some changes to the code as some scenarios are not working. E.g if we have 21 tasks instead of 20, the last one is left without being assigned. I modified the code and it's working fine.

Is there a chance to have generate a report at the end of the process?
e.g:
Calculated 4.2 6.3 4.2 6.3
Actual 4 6 4 7

This helps me to change the one additional task that falls into the last employee's bucket. I can do it manually.

Thank you very much for your help.

cheers,
Ravi.
Click to expand...

Hi Ravi,

What changes you made.Please confirm.

Regards
Raja T


Regards
Raja T
 
Abdul Matheen said:
Hi Ravi,

Please see the code below for your issue, and also i have attached the workbook for your reference.

Please let me know if you need further assistance.

Code:

Sub EmpTask()

Dim i As Integer
Dim perc As Integer

Sheets("sheet2").Select
Range("a2").Select
Selection.End(xlDown).Select
cnt = ActiveCell.Row - 1

Sheets("sheet1").Select

If Cells(1, "f").Value = 0.01 Then
i = 2
k = 2
perc = 0
Do While Cells(i, 1).Value <> ""
emp = Cells(i, 1).Value
perc = (Cells(i, 2).Value * cnt) + perc

For j = k To perc + 1
Sheets("sheet2").Select
Cells(k, 2).Value = emp
k = k + 1
Next j
i = i + 1
Sheets("sheet1").Select
Loop

Else
MsgBox "Please assign the task properly"
Exit Sub
End If

MsgBox "Task Completed...look in sheet2"

End Sub



Regards
Abdul Matheen
Click to expand...
Hi Thanks for the help. I have one question. In my case, no of cases change and no of users but the I need to divide them equally amongst users. Please let me know what changes need to be made.
Thanks
 
Abdul Matheen said:
Hi Ravi,

i have checked at my end but it is showing me the correct assignment if it 21 also. check once again i have attaching the workbook again.

Regards
Abdul Matheen
Click to expand...

Can we do status wise assignment with count of task ?
For example b,c,d column have task status like High,low, medium
And we updated count of task like 2 task of high status given to John and 3 task of medium status given to Ken sheet2 aslo have taks status when macro run emp name auto update
 

Attachments

  • Screenshot_20220328-235503_Gallery.jpg
    Screenshot_20220328-235503_Gallery.jpg
    613 KB · Views: 5
Status
Not open for further replies.
Back
Top