PDA

View Full Version : How to set 2 condition in VBA



Jagdev
01-02-2015, 04:49 AM
Hi Experts

I have created a macro which assign the work to the number of individuals in my team. We have a fixed criteria to assign task to an individual. The criteria is one task will be assigned to one individual only. The attached macro is fulfilling this condition. I am looking to set one more condition to the exsisting criteria. In case an individual receive more than 20+ tasks than he/she should not be assigned another task. In short the cycle should remove his name from the list.

Say from the attachment - 05030A12 task reference number if the count exceed 20 than Akshay should not be assigned any further task and the rest will be tasks should be assigned to other individuals. The same rules applies to rest of the individuals.

Regards,
Jaggi

Bob Phillips
01-02-2015, 11:49 AM
Try this


Const USE_ONLY_IF_AVAILABLE As Boolean = True
Const TASK_NUM_LENGTH As Long = 6
Const TASK_MAX As Long = 20

Public Sub Assign_Available_Individuals()
Dim TasksList As ListObject
Dim TasksRefNo As ListColumn
Dim TasksAssigned As ListColumn
Dim TaskReferences As Range
Dim CurrCell As String, PrevCell As String, Person As String
Dim Individuals() As String
Dim CurrIndex As Long, MaxIndex As Long
Dim i As Long

Set TasksList = Me.ListObjects("Tasks_Table")
Set TasksRefNo = TasksList.ListColumns("Task Reference Number")
Set TasksAssigned = TasksList.ListColumns("Assigned To")

ReDim Individuals(1 To [People].Rows.Count)

For i = 1 To UBound(Individuals)
If (Not (USE_ONLY_IF_AVAILABLE) Or (LCase(Left([Available].Cells(i).Value, 1)) = "y")) Then
MaxIndex = MaxIndex + 1
Individuals(MaxIndex) = [People].Cells(i).Value
End If
Next

Set TaskReferences = TasksRefNo.Range
With Me.Sort
.SortFields.Clear
.SortFields.Add Key:=TaskReferences, SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
.SetRange TaskReferences
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Set TaskReferences = TasksRefNo.DataBodyRange
CurrIndex = LBound(Individuals)
i = 1
PrevCell = ""
TasksList.ListColumns("Assigned To").DataBodyRange.ClearContents
For Each cell In TaskReferences
CurrCell = Left(cell, TASK_NUM_LENGTH)

If CurrCell <> PrevCell Or Application.CountIf(TasksAssigned.DataBodyRange, Person) >= TASK_MAX Then
PrevCell = CurrCell
Do
Person = Individuals(CurrIndex)
CurrIndex = CurrIndex + 1
If CurrIndex > MaxIndex Then CurrIndex = 1
Loop Until Application.CountIf(TasksAssigned.DataBodyRange, Person) < TASK_MAX Or CurrIndex > MaxIndex
End If
TasksAssigned.DataBodyRange.Cells(i).Value = Person
i = i + 1
Next
End Sub

Jagdev
01-04-2015, 10:47 PM
Hi xld

Thanks for the above code. It fulfill the upper limit requirement of my query, but the issue in case the tasks count is 30, the rest of the 10 task is assigned to the next individual from the list. It is not fulfilling the cretiria number 1 that is particular task entirely handle by an individual. Please let me know if we can do something to it. I tried increasing the upper limit to 30, it is working fine, but again if a task come with number 40. Is there anyway we can handle this situation.

Can we set a condition if the count increase the set upper limit that particular task should be assigned to another individual.

Regards,
JD

Bob Phillips
01-05-2015, 12:54 AM
Problem is that other individuals are already assigned by then., But what happens if 1 task has more items than you allow one individual to perform?

Jagdev
01-05-2015, 01:41 AM
Hi xld

Yes, if 1 task has more iteams than we allow one individual to perform it. It is a set criteria.

The concept here is an individual who works on a particular task knows the depth knowledge about it instead of the other one whom the few/remaining tasks are assigned in such cases.

Earlier depend upon task number we assign task to the group of ppl. In such cases someone receives more and the other just few, because the task count per case vary. So I am looking to amend the macro so that all the ppl in the team should have ideally close to same count of tasks

The core task should be handle by one individual.

Regards,
JD

Jagdev
01-05-2015, 09:04 PM
Hi xld

I was thinking is it possible say a task is assigned to an individual with the count of 30 and we set the upper limit to 20. The macro allow assign all the similar tasks to an individual and then restruct him/her any other task from the list. I am not sure if that is possible or not. I think it would look like dynamic upper limit.

Regards,
JD

Jagdev
01-06-2015, 06:28 AM
Hi Xld

Please let me know your view on the above thread.

Regards
JD

Jagdev
01-10-2015, 06:36 AM
Hi Experts

Any view on the above thread.

Regards
JD