• 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.

Need VBA or Formula for assigning tasks

AirenA

New Member
Hi All,

I am new to the forum so apologies if this is in the wrong place, at work at the start of the month we receive a list of tasks for the month, this list doesn change during the month and always covers the whole month. I am looking for a way to allocate tasks in an even (or even as possible fashion) between employees. I have attached a sample of the sheet I am currently working with.

A report is run at the beginning of the month which gives me the results in Sheet 1 on attached. I want to be able to copy the information from this report over to a workbook whereby in the statistics sheet I can click allocate and this will allocate an even number of tasks between employees, showing on sheet 1 who the task has been allocated to.

I think I have formulas to cover the rest of the stuff I would need but really could do with a way to allocate an even number of tasks between employees.
 

Attachments

  • Task Template.xlsx
    17.5 KB · Views: 15
Hi !​
According to your attachment a very beginner starter VBA demonstration​
(like you can yet operate manually without any code, faster than the time you used to create this thread !) :​
Code:
Sub Demo1()
    V = Range([Statistics!A7], [Statistics!A6].End(xlDown)).Value2
    [Tasks!D2].Resize(UBound(V)).Value2 = V
    [Tasks!D2].Resize(UBound(V)).AutoFill Range([Tasks!D2], [Tasks!C1].End(xlDown)(1, 2)), xlFillCopy
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Code:
Option Explicit

Sub Assign()
    Dim lr As Long, EE As Long, i As Long, x As Long, j As Long
    Dim s1 As Worksheet, s2 As Worksheet, lr2 As Long
    Set s1 = Sheets("Tasks")
    Set s2 = Sheets("Statistics")
    Application.ScreenUpdating = False
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
    EE = WorksheetFunction.CountA(s2.Range("A7:A" & lr2))
    x = Int((lr - 1) / EE)
    j = 7
    For i = 2 To lr Step x
        s1.Range("D" & i) = s2.Range("A" & j)
        j = j + 1
    Next i
    For i = 2 To lr
        If s1.Range("D" & i) = "" Then
            s1.Range("D" & i) = s1.Range("D" & i - 1)
        End If
    Next i
    Application.ScreenUpdating = True
    MsgBox "Complete"
End Sub
 
Without VBA, formula in cell D2 of the Tasks sheet:
=INDEX(Statistics!$A$7:$A$12,MOD(ROW(),6)+1)
copied down, convert to values with copy paste-special|Values.
Not very good because if the number of employees changes then you'll have to adjust the red parts of the formula.
 
I thought about pretty the same formula but for the same reason I prefered the easy VBA way …​
I prefer a smart enough sheet with headers starting in row #1 !​
Anyway as it is, the 6 red part can be replaced by a count of column A elements # minus 5 and the 12 red part just with the count …​
 
Back
Top