Locating Duplicates

[Taisey]

I've created a resource allocation sheet tasking the several people working with me to certain jobs. What I want it to do, is ensure that nobody has the same job more then 3 times in a week.

I'm new at this, so please ignore the repetitions and superfluities.

It currently works, except for the no more then 3 times in a week thing. Here's my sub for assigning jobs:

-----------------------------------------------------------------------

Sub AssignRandomChoreToAgent(rowMax, sheetName)
Dim strChore
Dim intRowjobnumbers
Dim intTempCount
Dim intNumber, intNewNumber, idx

For intRowjobnumbers = 2 To rowMax
DoEvents
Randomize
intNumber = Int((rowMax - 2 + 1) * Rnd + 2)
'-2, +1 because the cells start on row 2, +1 for the rnd function


'MsgBox CStr(intNumber)

If Worksheets(sheetName).Cells(intNumber, 2).Value = "" Then
'assign duty here
Call Assignbypriority(intNumber, sheetName)
Else
'duty already assigned go to next unassigned agent
For idx = 1 To rowMax - 2
intNewNumber = intNumber + idx
If intNewNumber > rowMax Then
intNewNumber = 2
End If

If Worksheets(sheetName).Cells(intNewNumber, 2).Value = "" Then
'Worksheets(sheetName).Cells(intNewNumber, 2).Value = "Assigned"
Call Assignbypriority(intNewNumber, sheetName)
Exit For
End If

Next
'make sure less then RowMax (number of agents)
'make sure next agent not already assigned

End If


Next

End Sub
______________________________________________________________

Sub Assignbypriority(agentIndex, inSheetName)
Dim strChore
Dim intRowjobnumbers
Dim intTempCount, intRowMax
intRowMax = Worksheets(inSheetName).Range("H8").CurrentRegion.Rows.Count
For intRowjobnumbers = 2 To intRowMax
intTempCount = CInt(Worksheets(inSheetName).Cells(intRowjobnumbers, 9).Value)
If intTempCount > 0 Then

Worksheets(inSheetName).Cells(intRowjobnumbers, 9).Value = intTempCount - 1
DoEvents
strChore = Worksheets(inSheetName).Cells(intRowjobnumbers, 8).Value
Exit For
End If
Next

Worksheets(inSheetName).Cells(agentIndex, 2).Value = strChore

End Sub

______________________________________________________________





There may be more things called throughout there, but I figured there is enough information above for you folks that know what you're doing to help.



Again, All I want is for this thing to make sure the jobs are assigned 3 out of 5 time, maximum.



Welcome me to the VB world, this is day 3

 

[Taisey]

Aargh, formatting disappeared, but things are indented accordingly.