macro to filter a column and fill text on other column

E

excelhelpseeker

New member
Joined
Jan 7, 2015
Messages
29
Reaction score
0
Points
0
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 macroView attachment sample allocation.xlsx
 
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
 
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
 
You must log in or register to reply here.
Back
Top