How to generate a list of prime number?

[Eric]

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

 

[Mike H]

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]

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]

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

 

[Mike H]

Eric,

I may have misread you post. If you want it to work for A1 only as opposed
to Column A then substitute this line
If Not Intersect(Target, Range("A:A")) Is Nothing Then
with this line
If Target.Address = "$A$1" Then
Mike

 

[Eric]

Thank you very much for your suggestions

I can type the number 2 in any cell rather than cell A1.
If I apply following code, could you please tell me where I should type the
number and I want to get the result in cell B1.
Do you have any suggestions?
Thank you very much
Eric

 

[Eric]

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

 

[Gary''s Student]

Try:

Sub subit()
For Each cm In ActiveSheet.Comments
cm.Text Text:=Replace(cm.Text, "linear", "exponential")
Next
End Sub

 

[Eric]

Thank everyone for suggestions
Should I paste following codes into macro or worksheet code?
Thank everyone for any suggestions
Eric

 

[Mike H]

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]

I work now
Thank everyone very much for suggestions
Eric :>

 

[Gary''s Student]

Ignore my previous post

 

[Mike H]

well that was easy!! thanks for the feedback

I work now
Thank everyone very much for suggestions
Eric :>

 

[Dana DeLouis]

For x = 1 To 10000

Dim i As Long

Could I suggest moving the Dim statement to the beginning so that it's
called only once?

Dim i As Long
For x = 1 To 10000

 

[Mike H]

Dana

Thanks for that you are of course correct. It gets very slow as numbers get
large so another improvement is to loop from2

For x= 2 to 10000
this allows the test for <2 to be eliminated
If (x <> 2 And x Mod 2 = 0) Or x <> Int(x) Then GoTo 100

Mike

 

[Eric]

I face with one problem, if I set a formula to return a value in cell A1,
such as
In cell A1, =A2+A3, when I change any value in cells A2 or A3, the return
prime number in cell B1 cannot be automatically updated. Do you have any
suggestions?
Thank everyone for any suggestions
Eric

 

[Bernd P]

Hello,

Why don't you just store the first 65536 primes in a worksheet and
look them up later (see: http://primes.utm.edu/)?
At a little cost of storage space you can gain some speed ...

Regards,
Bernd