Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to Query SQL Server with a parameter value (entered into an Input Box), and have that value also display in a selected cell on a worksheet | Excel Discussion (Misc queries) | |||
Modify Macro Code Depending on Excel Version | Excel Discussion (Misc queries) | |||
when I type a zip code into a cell, only zeros show up. Help? | New Users to Excel | |||
How do I input zeroes in a zip code for a mail merge? | Excel Worksheet Functions | |||
[Q] Save As throws type mismatch error in control's code? | Excel Discussion (Misc queries) |