mardi 31 août 2021

VBA - How do I randomly select 10% of rows from a column, ensuring they are different and put a Y in column B?

I am looking to randomly select 10% of tasks worked by different users ('originator' Column P) and place a Y in column B to allow checkers to QC the work. If the 10% is not a whole number then I am required to round up i.e. 0.8 would require 1 row and 1.3 would require 2 rows.

I am new to coding I have been able to add code to filter the rows to show the required date and the 'Originator' in column P then name this range as "userNames". I am not sure how to code to select the random 10%. I have changed the part I am struggling with to bold below.

Sub randomSelection()

Dim dt As Date
dt = "20/08/2021"


Dim lRow As Long


'Format date
    Range("J:J").Select
    Selection.NumberFormat = "dd/mm/yyyy"
    
 'Select User Grogu

    ActiveSheet.Range("$A$1:$W$10000").AutoFilter 10, Criteria1:="=" & dt
    ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=16, Criteria1:= _
        "SW\Grogu"
        
'Name range "userNames"
  With ActiveSheet
  
  lRow = .Cells(Rows.Count, 16).End(xlUp).Row
  If lRow < 3 Then Exit Sub
  
  .Cells(1, 16).Offset(1, 0).Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Select
  End With

 Selection.Name = "userNames"
 
**'Randomly select 10% of rows from originator and put a Y in column B**
 
'remove all defined names

    On Error Resume Next
    ActiveWorkbook.Names("userNames").Delete

 'Select User Finn

    ActiveSheet.Range("$A$1:$W$10000").AutoFilter 10, Criteria1:="=" & dt
    ActiveSheet.Range("$A$1:$W$10000").AutoFilter Field:=16, Criteria1:= _
        "SW\Finn"
        
'Name range "userNames"
  With ActiveSheet
  
  lRow = .Cells(Rows.Count, 16).End(xlUp).Row
  If lRow < 3 Then Exit Sub
  
  .Cells(1, 16).Offset(1, 0).Resize(lRow - 1).SpecialCells(xlCellTypeVisible).Select
  End With

 Selection.Name = "userNames"
 
'remove all defined names

    On Error Resume Next
    ActiveWorkbook.Names("userNames").Delete
    
    'Formate Date back
    Range("J:J").Select
    Selection.NumberFormat = "yyyy-mm-dd"

End Sub



Aucun commentaire:

Enregistrer un commentaire