# Generate random non-prime numbers

#### inarbeth

##### Well-known Member
I want to be able to run a macro that will generate in cells A2:B21 random non-prime numbers between 2 and 400. Can this be done?

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Just out of curiosity, why?

I am teaching my daughter maths and have created worksheets to test adding, subtraction and multiplication. I want to produce a sheet to do division and need non-prime numbers so that they are divisible.

Hi,

Preselect a "x" Rows by 1 Column range. Then, in the formula bar type

(I chose 8 samples from the range 1 to 30)

=NonPrimeSampleNoReplace(8, 1, 30)

Array enter this worksheet array by hitting Ctrl+Shift simultaneously with Enter.

The Folllowing UDFs are used. This has been lightly tested, so please post back with any errors you encounter.

Rich (BB code):
``````Function IsPrime2(Number As Double) As Variant
Dim x As Long, dbl As Double, lngUp As Long

''''    Author Dave Braden et al, posted to MS Excel Newsgroups
''''    off&selm=375716DB.51808223%40fiastl.net&rnum=10

If Number <> Int(Number) Then
IsPrime2 = CVErr(xlErrValue)
Else
Number = Abs(Number)
If Number < 2 Then
IsPrime2 = False
ElseIf Number / 2 = Int(Number / 2) Then
IsPrime2 = Number = 2
ElseIf Number < 9 Then
IsPrime2 = True
Else
lngUp = Int(Sqr(Number))
IsPrime2 = True
For x = 3 To lngUp Step 2
dbl = Number / x
If dbl = Int(dbl) Then
IsPrime2 = False
Exit For
End If
Next x
End If
End If
End Function

Function NonPrimeSampleNoReplace(SampleSize As Long, LowerBound As Long, _

''''  Author:  Jay Petrulis

Dim PopulationCollection As New Collection
Dim SampleArray() As Long
Dim temp As Long
Dim i As Long
Dim j As Long

Randomize

If UpperBound < LowerBound Then
temp = UpperBound
UpperBound = LowerBound
LowerBound = temp
End If

If (SampleSize > (UpperBound - LowerBound + 1) Or SampleSize <= 0) Then
NonPrimeSampleNoReplace = CVErr(xlErrValue)
Exit Function
End If

ReDim SampleArray(1 To SampleSize) As Long

For i = LowerBound To UpperBound
If Not IsPrime2(CDbl(i)) Then
End If
Else
If Not IsPrime2(CDbl(i)) Then
End If
End If
End If
Next i

For i = 1 To SampleSize
With PopulationCollection
j = Int(Rnd * .Count) + 1
SampleArray(i) = .Item(j)
.Remove j
End With
Next i

Set PopulationCollection = Nothing
NonPrimeSampleNoReplace = Application.Transpose(SampleArray)
End Function``````

Hi Jay,
Thanks for that. I had a problem selecting the whole range first because that created an array that I could not edit. However, I got round it by selecting the first cell, Ctrl+Shift+Enter and autofilling down. I have managed to do what I set out to and my daughter is amazed!

Many thanks
Ian

Hi,

If you resample with your method, you may get duplicate entries.

Create the worksheet array, and then when you want new items, highlight the range again, hit the F2 key, and then Ctrl+Shift+Enter. You will get a new data set.

Also, if you want to continue in other cells, but don't want any repeats, there is an optional 4th argument. Put the exiting range as the optional 4th argument and the existing entries will be excluded from the next set as well.

The following procedure will :
1. Put random numbers in the range A2:b21
2. clear out old numbers from the indicated range
3. Make sure all numbers are unique
4. make sure none of the numbers are Prime

Public Sub RandNum()
Randomize
Range("A2:B21").Clear
For Each c In Range("A2:B21")
TryAgain: c.Value = (Int((99 * Rnd) + 1))
If Application.WorksheetFunction.CountIf(Range("A2:B21"), c.Value) > 1 Or _
Next c
End Sub
M = Application.WorksheetFunction.Ceiling(Sqr(Num), 1)
Do While M - cnt > 1
On Error Resume Next
If (Num Mod (M - cnt)) = 0 Then IsPrimeNumber = False
cnt = cnt + 1
Loop
If Num > 3 Then
If (Num Mod 3) = 0 Then IsPrimeNumber = False
If (Num Mod 2) = 0 Then IsPrimeNumber = False
Else
If Num = 2 Then IsPrimeNumber = True
End If
End Function

TO INSTALL:
1. Select the sheet you want code to work in
2. right click the the "Name Tab" of sheet
3. Select "View Code" in drop down menu
4. VBE window will open ... paste code in and exit VBE

TO RUN MY CODE:
1.While the sheet to evaluate is active goto tool bar
2. ON tool bar goto "Tools...Macro....Macros"
3. A list of Macros should appear
4. To run the above Macro Double Click on it's name in the list

Hi,
Let me add a solution although it doesnt meet exactly what you wanted (not includes 2 and has repeats)

=RANDBETWEEN(2,20)*RANDBETWEEN(2,20)
But it is simple to handle.

Eli

I know this is the opposite of what you asked for but out of interest J Walkenbach has a downloadable workbook on this page that generates Prime Numbers. It has a nice little demo as well.

Replies
9
Views
245
Replies
2
Views
192
Replies
11
Views
481
Replies
3
Views
55
Replies
4
Views
111

1,207,011
Messages
6,076,145
Members
446,187
Latest member
LMill

### 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.

### Which adblocker are you using?

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

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