Random Audit Help

  • Thread starter Thread starter BZabrocki
  • Start date Start date
B

BZabrocki

I am currently building a spreadsheet that will generate random
preventative maintenance work order numbers to audit each week for ten
percent of the PM's generated (the amount generated varies but appox.
250). I currently cut and paste all the PM's from a database and
hilight them all manually off of a rand function.

I need a macro to do the following:
1. Count Column A = Y
2. Y * 10% = X
3. Rand between row (2,Y) = Z
4. Fill color on Z
5. Loop X amount of times

Thanks in advance for any help
 
Try this

Sub RandomAudits()

'I need a macro to do the following:
'1. Count Column A = Y
'2. Y * 10% = X
'3. Rand between row (2,Y) = Z
'4. Fill color on Z
'5. Loop X amount of times

Dim i As Long
Dim countY As Long
Dim percentX As Long
Dim randZ As Long

' clear previous highlights
Cells.EntireRow.Interior.ColorIndex = 0

'1. Count Column A = Y
countY = WorksheetFunction.CountIf(Range("A:A"), "y")

'2. Y * 10% = X
percentX = countY * 10 / 100

'5. Loop X amount of times
For i = 1 To percentX

'3. Rand between row (2,Y) = Z
Randomize
randZ = Int((countY * Rnd) + 2) ' Generate random value between 2 and
Y

'4. Fill color on Z
Range("A" & randZ).EntireRow.Interior.ColorIndex = 3

Next 'percentX

End Sub

Regards

Trevor
 
Just out of interest, what did the code you ended up with look like ?

Regards

Trevor
 
Back
Top