Consulting

Results 1 to 3 of 3

Thread: Macros/VBA to do a loop and insert text

  1. #1
    VBAX Newbie
    Joined
    Jun 2014
    Posts
    2
    Location

    Macros/VBA to do a loop and insert text

    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.
    Attached Files Attached Files

  2. #2
    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

  3. #3
    VBAX Newbie
    Joined
    Jun 2014
    Posts
    2
    Location
    I know it's a relally late reply. Thanks a lot. It did work with a little bit of modifications.

    Thanks a ton.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •