#### brianfosterblack

##### Board Regular

- Joined
- Nov 1, 2011

- Messages
- 133

In Cell B1 I enter the number of rows I want to fill.

This VBA ensures that each number is only selected once

The problem I have is that sometimes Cell B1 is a larger number than cell B2 so I want to be able to enter in cell C1 (or enter a formula) the maximum number of times a number can be repeated

so if the maximum number I want is 10 but I want to fill 20 rows, then in cell C1 I will enter 2 which means the numbers 1 to 10 can be repeated twice.

Is there any way of adjusting this code to allow this.

Option Explicit

Option Base 1

Public jj As Long

Sub Resample() ' with the doublesort,numbers randomly found between the range can only appear once

Dim i As Long

Dim hold(10000) As Single, Hold2(10000) As Single

Application.Calculation = xlCalculationManual

Range("NumberRange").ClearContents

Range("A2").Select

Randomize

For i = 1 To Range("A1").Value

Hold2(i) = i

Next i

For jj = 1 To 1

For i = 1 To Range("A1").Value

hold(i) = Rnd

Next i

Call DoubleSort(Range("A1").Value, hold, Hold2)

For i = 1 To Range("B1").Value

ActiveCell.Offset(1, 0).Select

ActiveCell = Hold2(i)

Next i

Next jj

Application.Calculation = xlCalculationAutomatic

Range("A1").Select

End Sub

'***********************************************************************

'* Sorting Process - Sort array y based on array x *

'***********************************************************************

Sub DoubleSort(n As Long, x() As Single, y() As Single)

Dim xTemp As Double

Dim yTemp As Double

Dim i As Long

Dim j As Long

For j = 2 To n

xTemp = x(j)

yTemp = y(j)

For i = j - 1 To 1 Step -1

If (x(i) <= xTemp) Then GoTo 10

x(i + 1) = x(i)

y(i + 1) = y(i)

Next i

i = 0

10 x(i + 1) = xTemp

y(i + 1) = yTemp

Next j

End Sub