Help on coding

[Eric]

Referring to the post in General Question

Does anyone have any suggestion on following coding?

Following code is to generate a prime number in cell B1 by typing a number
in cell A1, if I type 1 in cell A1, then it will return the first prime
number 2 in cell B1,
if I type 2 in cell A1, then it will return the first prime number 3 in cell
B1,
I would like to set a formula to change the number 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? I still want to return the prime number in cell B1 based on the
value in cell A1. A1=A2+A3 is just a simple example for formula.
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.

 

[Mike H]

Eric,

A couple of changes to the code will achieve that. Paste this in exactly as
below. I've made a couple of changes to the code to speed it up:-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("A2:A3")) Is Nothing Then
'If Target.Address = "$A$1" Then
If IsNumeric(Target) Then
On Error Resume Next
For x = 2 To 100000
If (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 = Cells(1, 1).Value Then
Cells(1, 2).Value = x
Exit Sub
100
End If
Next
On Error GoTo 0
End If
End If
End Sub

Mike

 

[p45cal]

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Or IsEmpty(Range("A1")) Then Exit Sub
Set isect = Intersect(Target, Range("A2:A3"))
If Not isect 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 = Range("A1").Value Then
Range("B1").Value = x
Exit Sub
100
End If
Next
On Error GoTo 0
End If
End If
End Sub

 

[Eric]

Thank you very much for your reply

I would like to determine the value in cell A1 based on following formula
=LEN(CELL("filename",Z1)), which will return the value from the filename for
a number of char into cell A1. Firstly, I delete the value in cell B1, save
and close the file. After I open the file again, I expect the prime number in
cell B1 will be showed based on the codes, but it does not show, maybe the
value in cell A1 is changed based on formula rather than directly enter
through keyboard.
Do you have any suggestions on how to re-calculate the prime number based on
the formula?
Thank you very much for any suggestions
Eric

 

[Eric]

Thank you very much for your reply

I would like to determine the value in cell A1 based on following formula
=LEN(CELL("filename",Z1)), which will return the value from the filename for
a number of char into cell A1. Firstly, I delete the value in cell B1, save
and close the file. After I open the file again, I expect the prime number in
cell B1 will be showed based on the codes, but it does not show, maybe the
value in cell A1 is changed based on formula rather than directly enter
through keyboard.
Do you have any suggestions on how to re-calculate the prime number based on
the formula?
Thank you very much for any suggestions
Eric

 

[p45cal]

The worksheet_change event does not fire when a calculation generates a
differen value for a calculation, at least not in Excel 2003.
You may need to use a different event, there's bound to be one that's
applicable:
To view the event procedures for a sheet, right-click the sheet tab and
click View Code on the shortcut menu. Select the event name from the
Procedure drop-down list box.
To view the event procedures for a workbook, right-click the title bar of a
restored or minimized workbook window (not the Excel application window) and
click View Code on the shortcut menu. Select the event name from the
Procedure drop-down list box.