View Full Version : Macros/VBA to do a loop and insert text

06-19-2014, 06:55 PM
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.

06-20-2014, 02:17 AM
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

08-11-2014, 09:09 PM
I know it's a relally late reply. Thanks a lot. It did work with a little bit of modifications.

Thanks a ton.