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