Referring to General Question

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.

You code only runs when A1 is modified and the event will not be triggered by
a change in a formula result. Why not trigger off of changes in A2 or A3?

HTH,
Barb Reinhardt

"Eric" wrote:

Referring to General Question

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.