Help pulling random sample, please?

tjroby

New Member
Joined
Jan 31, 2013
Messages
10
I'm having trouble with random sampling again. I've created an example spreadsheet to test this with in a way that I can easily see if I am getting duplicates or not. I'm getting duplicates!

Here's the code:

Code:
Option Explicit
Sub Random_Test()
    Dim i       As Long
    Dim ii       As Long
    Dim nItem   As Long
    Dim iItem   As Long
    Dim vRnd    As Variant
    Dim rList   As Range
    Dim rSamp   As Range
    Dim shArr
    
    shArr = Array("BFTE", "CFTE")
    With Sheets.Add
        .Name = "To Survey"
        .Range("A1").FormulaR1C1 = "BFTE bud ID"
        .Range("C1").FormulaR1C1 = "CFTE bud ID"
    End With
       
    ii = 1
    For i = 0 To UBound(shArr)
        With Worksheets(shArr(i))
            nItem = (Application.WorksheetFunction.CountA(.Range("D4", .Range("D65536").End(xlUp)))) * 0.1
            Set rList = .Range("E4").CurrentRegion
        End With
        With Worksheets("To Survey")
            Set rSamp = .Cells(Rows.Count, ii).End(xlUp).Offset(1, 0).Resize(nItem)
            vRnd = RandBetw(1, rList.Rows.Count)
            For iItem = 1 To nItem
                rSamp(iItem, 1) = iItem
                rSamp(iItem, 1) = rList(vRnd(iItem), 5)
            Next iItem
        End With
    ii = ii + 2
    Next
    
    Sheets("To Survey").Columns("A:C").EntireColumn.AutoFit
    
End Sub

Please help!
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Forum statistics

Threads
1,214,833
Messages
6,121,857
Members
449,051
Latest member
excelquestion515

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top