Simple Randomizer

[Bryancarlo]

Hi,

Im trying to create a macro based randomizer. I wish to randomize samples in the "Population" sheet (Column A). After clicking "Randomize", random samples based from Required Sample % or Required Sample Count will be pasted in the "Samples" sheet Column A. Numbers in Required Sample % and Required Sample Count are manually entered depending on the need.

Hoping for your help.

 

[Legacy 143009]

What does "Required Sample %" mean?

 

[Bryancarlo]

What does "Required Sample %" mean?

Hi, for my example, 20%, it identifies 20% of the total population which will be picked randomly and pasted in Samples sheet. Thank you

 

[Legacy 143009]

Let say you have 20 items. %20 means, you are going to pick 4 items. But Count says 5. Which rule to pick? I am confused.

 

[Bryancarlo]

Let say you have 20 items. %20 means, you are going to pick 4 items. But Count says 5. Which rule to pick? I am confused.

Sorry bout the confusion. Either we do Required Sample % or Required Sample Count. We cant use both at the same time

 

[Legacy 143009]

One last question. Are you entering %20 as "0,20" and later formatting as perfentage? Or is it directly number 20?

I hope I am clear.

 

[Bryancarlo]

One last question. Are you entering %20 as "0,20" and later formatting as perfentage? Or is it directly number 20?

I hope I am clear.

Hi, sorry for the late revert. It's directly number 20. Thanks

 

[Bryancarlo]

Anyone who can help me with this?

 

[Legacy 143009]

Hello!

Sory for the late reply. This should work for you:

Sub test()
  Dim myRange As Variant, percentage As Integer, c As Integer, i As Long, rnd As Integer
  With Worksheets("Sheet1")
  myRange = .Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
  percentage = .Range("C2").Value
  c = IIf(.Range("C2").Value > 0, UBound(myRange, 1) * (percentage / 100), .Range("D2").Value)
  End With
  With Worksheets("Sheet2")
  .Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row).Clear
  For i = 1 To c
    Do
      rnd = WorksheetFunction.RandBetween(1, UBound(myRange, 1))
    Loop While Not IsError(Application.Match(myRange(rnd, 1), .Range("A:A"), 0))
    .Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = myRange(rnd, 1)
  Next
  End With
End Sub

 

[Bryancarlo]

Hello!

Sory for the late reply. This should work for you:

Sub test()
  Dim myRange As Variant, percentage As Integer, c As Integer, i As Long, rnd As Integer
  With Worksheets("Sheet1")
  myRange = .Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row)
  percentage = .Range("C2").Value
  c = IIf(.Range("C2").Value > 0, UBound(myRange, 1) * (percentage / 100), .Range("D2").Value)
  End With
  With Worksheets("Sheet2")
  .Range("A2:A" & .Cells(Rows.Count, "A").End(xlUp).Row).Clear
  For i = 1 To c
    Do
      rnd = WorksheetFunction.RandBetween(1, UBound(myRange, 1))
    Loop While Not IsError(Application.Match(myRange(rnd, 1), .Range("A:A"), 0))
    .Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = myRange(rnd, 1)
  Next
  End With
End Sub

oh my God! It works perfectly! Thank you very much!!