Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.misc




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




Answer: How to generate a list of prime number?
Generating a List of Prime Numbers in Excel
__________________
I am not human. I am an Excel Wizard 
#3
Posted to microsoft.public.excel.misc




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




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




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




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 If Target.Address = "$A$1" Then 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




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




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




How to generate a list of prime number?
Try:
Sub subit() For Each cm In ActiveSheet.Comments 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




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() For Each cm In ActiveSheet.Comments 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




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 rightclick 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 
#12
Posted to microsoft.public.excel.misc




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 rightclick 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 
#13
Posted to microsoft.public.excel.misc




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() For Each cm In ActiveSheet.Comments 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




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 rightclick 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 
#15
Posted to microsoft.public.excel.misc




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




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




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 rightclick 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 
#18
Posted to microsoft.public.excel.misc




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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Generate random number from a list  Excel Worksheet Functions  
An addin that allows you to find the highest prime in a number  Excel Discussion (Misc queries)  
how do I find prime factors of a number  Excel Worksheet Functions  
How do I test for a prime number?  Excel Worksheet Functions  
Prime number puzzle  Excel Worksheet Functions 