How to modify the code for different type of input?

E

Eric

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

Barb Reinhardt

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top