Macros/VBA to do a loop and insert text

[fazilps]

I have a sample data set like what I have at work. The file is attached.

The number of orders assisgned needs to be balanced. As in the sample data, some of the orders are already assisgned to a team.
Range J15:L27 determines how many orders have been already assisgned and how many more can be allocated.
What I would require the macros to do is start filling up Column C if it is blank, with the Team Name depending on which which WorkGroup is in column B.

I figured it could be done with one or two loops.
If L16 >0, find the next blank next to Workgroup1 and put "Team A". If L16 =0, move to next team member. Repeat till "To Be Assigned" for each member is 0 or no more orders remaining to be assigned.


I'm not an advanced VBA user, just know the basics. Any help would be highly appreciated.

Thanks in advance.

[ashleyuk1984]

Just made this up quickly for you. It could be better, but it does the job. :)



Sub TeamAllocation()


Dim LastRow As Integer
Dim x As Integer


LastRow = Range("B" & Rows.Count).End(xlUp).Row


For x = 2 To LastRow


If Range("C" & x).Value = vbNullString Then
If Range("B" & x).Value = "Work Group1" Then
If Range("L16").Value > 0 Then
Range("C" & x).Value = "Team A"
ElseIf Range("L17").Value > 0 Then
Range("C" & x).Value = "Team B"
ElseIf Range("L18").Value > 0 Then
Range("C" & x).Value = "Team C"
End If
End If


If Range("B" & x).Value = "Work Group2" Then
If Range("L25").Value > 0 Then
Range("C" & x).Value = "Team D"
ElseIf Range("L26").Value > 0 Then
Range("C" & x).Value = "Team E"
ElseIf Range("L27").Value > 0 Then
Range("C" & x).Value = "Team F"
End If
End If
End If


Next x




End Sub

[fazilps]

I know it's a relally late reply. Thanks a lot. It did work with a little bit of modifications.

Thanks a ton.