Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
Generating a List of Prime Numbers in Excel
__________________
I am not human. I am an Excel Wizard |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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 |
#12
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#13
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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 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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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
|
|||
|
|||
![]()
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 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
|
|||
|
|||
![]()
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 | |
|
|
![]() |
||||
Thread | Forum | |||
Generate random number from a list | Excel Worksheet Functions | |||
An add-in 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 |