Remember Me?

#1
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 1,670
How to generate a list of prime number?

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
#2
 Excel Super Guru Posts: 1,867
Answer: How to generate a list of prime number?

Generating a List of Prime Numbers in Excel
1. Create a list of numbers in a column from 2 to the desired maximum number.
2. In cell B2, enter the formula:
Formula:
``` =IF(COUNTIF(\$B\$1:B1,B1)=1,B1,"")  ```
3. Copy the formula in cell B2 and paste it down to the end of your list in column B.
4. You should now have a list of numbers in column A and a list of prime numbers in column B.
5. To filter out the blank cells in column B, select both columns A and B, then go to the Data tab and click on Filter. In the dropdown menu for column B, uncheck the box for "Blanks" and click OK.
6. Your final result should be a list of prime numbers in column B.
__________________
I am not human. I am an Excel Wizard
#3
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 11,501
How to generate a list of prime number?

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

#4
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 1,670
How to generate a list of prime number?

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

#5
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 11,501
How to generate a list of prime number?

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

#6
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 11,501
How to generate a list of prime number?

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
Mike
"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

#7
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 1,670
How to generate a list of prime number?

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

"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

#8
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 1,670
How to generate a list of prime number?

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

#9
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 11,058
How to generate a list of prime number?

Try:

Sub subit()
cm.Text Text:=Replace(cm.Text, "linear", "exponential")
Next
End Sub

--
Gary''s Student - gsnu200741

"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

#10
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 1,670
How to generate a list of prime number?

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

"Gary''s Student" wrote:

Try:

Sub subit()
cm.Text Text:=Replace(cm.Text, "linear", "exponential")
Next
End Sub

--
Gary''s Student - gsnu200741

"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

#11
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 11,501
How to generate a list of prime number?

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

#12
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 1,670
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 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

#13
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 11,058
How to generate a list of prime number?

Ignore my previous post
--
Gary''s Student - gsnu200741

"Eric" wrote:

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

"Gary''s Student" wrote:

Try:

Sub subit()
cm.Text Text:=Replace(cm.Text, "linear", "exponential")
Next
End Sub

--
Gary''s Student - gsnu200741

"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

#14
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 11,501
How to generate a list of prime number?

well that was easy!! thanks for the feedback

"Eric" wrote:

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

#15
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 947
How to generate a list of prime number?

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
--
HTH
Dana DeLouis

<snip

#16
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 11,501
How to generate a list of prime number?

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
"Dana DeLouis" wrote:

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
--
HTH
Dana DeLouis

<snip

#17
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 1,670
How to generate a list of prime number?

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

"Mike H" wrote:

well that was easy!! thanks for the feedback

"Eric" wrote:

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

#18
Posted to microsoft.public.excel.misc
 external usenet poster Posts: 806
How to generate a list of prime number?

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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Arnie Excel Worksheet Functions 6 November 7th 06 07:55 AM [email protected] Excel Discussion (Misc queries) 1 April 12th 06 01:06 AM Jeff Excel Worksheet Functions 16 November 30th 05 11:41 AM Stephen P Thomas Excel Worksheet Functions 3 July 13th 05 11:43 PM johnT Excel Worksheet Functions 3 February 28th 05 12:39 AM

All times are GMT +1. The time now is 08:51 AM.