Generate random non-prime numbers

[inarbeth]

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?

 

[Todd Bardoni]

Just out of curiosity, why?

 

[inarbeth]

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.

 

[Jay Petrulis]

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.

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
''''    http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&safe=
''''    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, _
UpperBound As Long, Optional AlreadyUsedArray)

''''  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 IsMissing(AlreadyUsedArray) Then
        If Not IsPrime2(CDbl(i)) Then
            PopulationCollection.Add i
        End If
    Else
        If IsError(Application.Match(i, AlreadyUsedArray, 0)) Then
            If Not IsPrime2(CDbl(i)) Then
                PopulationCollection.Add i
            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

 

[Jay Petrulis]

Hi,

Here is an example with your actual requested range...

Book1
	A	B	C	D
1	Numbers
2	74
3	333
4	114
5	63
6	260
7	273
8	93
9	46
10	231
11	291
12	346
13	28
14	90
15	209
16	255
17	244
18	162
19	339
20	86
21	178
Sheet1

 

[inarbeth]

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

 

[Jay Petrulis]

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.

 

[Nimrod]

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 _
IsPrimeNumber(c.Value) Then GoTo TryAgain
Next c
End Sub
Public Function IsPrimeNumber(Num) As Boolean
IsPrimeNumber = True
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

 

[eliW]

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

 

[parry]

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.