Solved: Help with random sampling code

[katie]

I'm trying to write a macro to use the random sampling function I found on this website. I want to take the number of values in column P and then sample that number from column R (without replacement) and have the output go to column S.

When I run the code as is (see attached) I dont get an error but no results appear in column S.

When I try and include Input and Take into the equation for the input range and take arguments I get an error message.

Please help!

Thanks,

Katie

[Bob Phillips]

katie,

The code works fine as is, but you just don't do anything with the results. If you change Macro1 to



Sub Macro1()
Dim Take As Integer
With Worksheets("Sheet1")
finalrow = .Cells(.Rows.Count, 18).End(xlUp).Row
NextCol = .Cells(1, .Columns.Count).End(xlToLeft).Column + 1
Take = .Cells(.Rows.Count, 16).End(xlUp).Row - 1
'Set IRange = .Range("R1").Resize(finalrow)
Set orange = .Range("S1").Resize(Take)
'ORange = Sample(IRange, Take, False, False)
orange = Sample(.Cells(1, 18).Resize(finalrow), 18, False, False)
End With
With Worksheets("Sheet2")
.Range("A1").Resize(UBound(orange) - LBound(orange) + 1) = Application.Transpose(orange)
.Activate
End With
End Sub


the results are on Sheet2

[katie]

This worked, thanks so much!

Katie