View Single Post
  #12   Report Post  
Posted to microsoft.public.excel.misc
Eric Eric is offline
external usenet poster
 
Posts: 1,670
Default How to generate a list of prime number?

I work now
Thank everyone very much for suggestions
Eric :

"Mike H" wrote:

Eric,

Follow these instrucyions precisly and it will work. Slect the shhet you
want to use and right-click the sheet tab. Select view code and paste this in
exacly as below:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Target.Address = "$A$1" Then
If IsNumeric(Target) Then
On Error Resume Next
For x = 1 To 10000
Dim i As Long
If x < 2 Or (x < 2 And x Mod 2 = 0) Or x < Int(x) Then GoTo 100
For i = 3 To Sqr(x) Step 2
If x Mod i = 0 Then GoTo 100
Next
foundprime = foundprime + 1
If foundprime = Target.Value Then
Target.Offset(0, 1).Value = x
Exit Sub
100
End If
Next
On Error GoTo 0
End If
End If
End Sub

Close the VB editor and then enter 50 in a1 and the 50th prime will be
displayed in B1.

Mike

"Eric" wrote:

I try to type 50 in cell A1, there is nothing in cell B1.
Could you give any suggestion how to run the code?
Does it run automatically by type 50 in cell A1?
Thank you for any suggestion
Eric

"Mike H" wrote:

Eric,

It's now worksheet code so rightclick a sheet tab and view code and paste
this in:-

A number enter into column A will cause the prime to be displayed in B. i.e
put 50 in A1 and you get the 50th prime in B1. If it doesn't display a number
then you put to higher number in colA

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If IsNumeric(Target) Then
On Error Resume Next
For x = 1 To 10000
Dim i As Long
If x < 2 Or (x < 2 And x Mod 2 = 0) Or x < Int(x) Then GoTo 100
For i = 3 To Sqr(x) Step 2
If x Mod i = 0 Then GoTo 100
Next
foundprime = foundprime + 1
If foundprime = Target.Value Then
Target.Offset(0, 1).Value = x
Exit Sub
100
End If
Next
On Error GoTo 0
End If
End If
End Sub

"Eric" wrote:

Thank you very much for suggestions

Will this code possible be written in excel coding?
For example, if I type 1 in cell A1, then the first prime number 2 will be
showed in cell B1. If I type 2 in cell A1, then the second prime number 3
will be showed in cell B1.
Do you have any suggestions?
Thank you very much for suggestions
Eric

"Mike H" wrote:

Eric,

Try this. It will loop from 1 to 1000 and list the primes within that range
to column A of the active sheet.

Sub mersible()
a = 1
For x = 1 To 1000 'Change to suit
Dim i As Long
If x < 2 Or (x < 2 And x Mod 2 = 0) Or x < Int(x) Then GoTo 100
For i = 3 To Sqr(x) Step 2
If x Mod i = 0 Then GoTo 100
Next
Cells(a, 1).Value = x
a = a + 1
100
Next
End Sub

Mike
"Eric" wrote:

Does anyone have any suggestions on how to generate a list of prime number in
excel? such as 2,3,5,7,11,13,17,23,29,31 ...
Does anyone know the formula?
Thanks in advance for any suggestions
Eric