How to modify the code for different type of input?

Giganews Newsgroups
Subject: How to modify the code for different type of input?
Posted by:  Eric
Date: Fri, 31 Aug 2007

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.

Replies