Results 1 to 3 of 3

Thread: macro to filter a column and fill text on other column

  1. #1

    macro to filter a column and fill text on other column



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi champs,

    I have 2 cols

    Col A - Processor name (blank)
    Col B - Status (F and M)

    i need to filter Col B and and for status "F", i need to count special cells in Col B.

    the count doesnot matter, but i need to assign 70 special cells to 7 users in Col A

    i.e after filter, in the available count of B column, 70 cells to user1, 70 to user2, 70 to user 3 and so on has to be assigned... please find the sample for the reference.

    pls help me with a macrosample allocation.xlsx

  2. #2
    Code:
    Public Sub AllocateUsers()Dim lastrow As Long
    Dim id As Long
    Dim cnt As Long
    Dim i As Long
    
    
        Application.ScreenUpdating = False
        
        With ActiveSheet
        
            lastrow = .Cells(.Rows.Count, "B").End(xlUp).Row
            id = 1
            For i = 2 To lastrow
            
                If .Cells(i, "B").Value = "F" Then
                
                    cnt = cnt + 1
                    If cnt > 70 Then
                    
                        id = id + 1
                        cnt = 0
                    End If
                    
                    .Cells(i, "A").Value = "user" & id
                End If
            Next i
        End With
        
        Application.ScreenUpdating = True
    End Sub

  3. #3
    thanks for the code. But the code assigns from user 1 to user 14...... as i need to fix only till user 7.

    and also user 1 is assigned with 70 rows.. that right ... but from user 2, 71 rows are assigned.. pls help

Posting Permissions

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