How to modify the code for different type of input?
 

Does anyone have any suggestions on how to modify following code?
I would like to set a formula to determine the value in cell A1 rather than
manually type any value in this cell. In this case, 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. Does anyone have any suggestions?
Thank everyone for any suggestions
Eric

===========================================
Coding
===========================================

Select the sheet 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.

How to modify the code for different type of input?
 

Give this a whirl... (it assumes that all of the precidint cells of A1 will
be on the same page as A1)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Long
Dim foundprime As Long

If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1").Precedents) 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
Range("B1").Value = x
Exit Sub
100
End If
Next
On Error GoTo 0
End If
End If
End Sub

--
HTH...

Jim Thomlinson


"Eric" wrote:

Does anyone have any suggestions on how to modify following code?
I would like to set a formula to determine the value in cell A1 rather than
manually type any value in this cell. In this case, 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. Does anyone have any suggestions?
Thank everyone for any suggestions
Eric

===========================================
Coding
===========================================

Select the sheet 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.

How to modify the code for different type of input?
 

Thank you for your suggestions
This line gets error
If Not Intersect(Target, Range("A1").Precedents) Is Nothing Then
Do you have any suggestions?
Thank you for any suggestions
Eric

"Jim Thomlinson" wrote:

Give this a whirl... (it assumes that all of the precidint cells of A1 will
be on the same page as A1)

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Long
Dim foundprime As Long

If Target.Cells.Count 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A1").Precedents) 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
Range("B1").Value = x
Exit Sub
100
End If
Next
On Error GoTo 0
End If
End If
End Sub

--
HTH...

Jim Thomlinson


"Eric" wrote:

Does anyone have any suggestions on how to modify following code?
I would like to set a formula to determine the value in cell A1 rather than
manually type any value in this cell. In this case, 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. Does anyone have any suggestions?
Thank everyone for any suggestions
Eric

===========================================
Coding
===========================================

Select the sheet 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.