Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code : Example 9-1 David Bourg's Book
Hi:
I am having a problem with one line of code from example 9-1 of David M. Bourg's book, "Excel Scientific and Engineering Cookbook". The Excel spreadsheet is named "'Nonlinear Equation". The "error" is isolated to one line of code. When I click on the button "Computer Cf" I get the error MsgBox "Run-time error '1004" Reference is not valid" Everything works except this line of code. I am having trouble finding the error...please advise. Thank you. Here is the line of code as presented in Listing 9-1, p. 280. .Range("Fx").GoalSeek Goal:=0, ChangingCell:=.Range("Cf") Here is my listing of the code subroutines associated with this example. Private Sub CommandButton1_Click() ComputeCf End Sub Public Sub ComputeCf() Dim inc As Double With Worksheets("Nonlinear Equation") inc = (.Range("Rn_2") - .Range("Rn_1")) / 20 For i = 0 To 20 .Range("Rn") = .Range("Rn_1") + (inc * i) .Range("Fx").GoalSeek Goal:=0, ChangingCell:=.Range("Cf") .Cells(10 + i, 2) = .Range("Rn") .Cells(10 + i, 3) = .Range("Cf") Next i End With End Sub Note: The following data have "cell names" defined in: Insert Name Define Rn_1 = 6000 ='Nonlinear Equation'!$C$1 Rn_2 = 12000 ='Nonlinear Equation'!$C$2 Rn = 6000 ='Nonlinear Equation'!$C$3 Cf = 0.0124 ='Nonlinear Equation'!$C$4 Fx = 3.77E-13 ='Nonlinear Equation'!$C$5 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code : Example 9-1 David Bourg's Book
GoalSeek requires that there be a dependency between the ChangingCell
and the cell it's applied to. If Fx is a constant (=4.77E-13), then changing Cf will NEVER result in Fx changing. So XL throws an error. Fx must have a formula in it that causes it to change when Cf changes. In article , DaleB wrote: Hi: I am having a problem with one line of code from example 9-1 of David M. Bourg's book, "Excel Scientific and Engineering Cookbook". The Excel spreadsheet is named "'Nonlinear Equation". The "error" is isolated to one line of code. When I click on the button "Computer Cf" I get the error MsgBox "Run-time error '1004" Reference is not valid" Everything works except this line of code. I am having trouble finding the error...please advise. Thank you. Here is the line of code as presented in Listing 9-1, p. 280. .Range("Fx").GoalSeek Goal:=0, ChangingCell:=.Range("Cf") Here is my listing of the code subroutines associated with this example. Private Sub CommandButton1_Click() ComputeCf End Sub Public Sub ComputeCf() Dim inc As Double With Worksheets("Nonlinear Equation") inc = (.Range("Rn_2") - .Range("Rn_1")) / 20 For i = 0 To 20 .Range("Rn") = .Range("Rn_1") + (inc * i) .Range("Fx").GoalSeek Goal:=0, ChangingCell:=.Range("Cf") .Cells(10 + i, 2) = .Range("Rn") .Cells(10 + i, 3) = .Range("Cf") Next i End With End Sub Note: The following data have "cell names" defined in: Insert Name Define Rn_1 = 6000 ='Nonlinear Equation'!$C$1 Rn_2 = 12000 ='Nonlinear Equation'!$C$2 Rn = 6000 ='Nonlinear Equation'!$C$3 Cf = 0.0124 ='Nonlinear Equation'!$C$4 Fx = 3.77E-13 ='Nonlinear Equation'!$C$5 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code : Example 9-1 David Bourg's Book
JE:
Excellent ! - this was the missing link. I am very new to Excel/VBA programming and must learn its ways. Thank you for your help ! DaleB "JE McGimpsey" wrote: GoalSeek requires that there be a dependency between the ChangingCell and the cell it's applied to. If Fx is a constant (=4.77E-13), then changing Cf will NEVER result in Fx changing. So XL throws an error. Fx must have a formula in it that causes it to change when Cf changes. In article , DaleB wrote: Hi: I am having a problem with one line of code from example 9-1 of David M. Bourg's book, "Excel Scientific and Engineering Cookbook". The Excel spreadsheet is named "'Nonlinear Equation". The "error" is isolated to one line of code. When I click on the button "Computer Cf" I get the error MsgBox "Run-time error '1004" Reference is not valid" Everything works except this line of code. I am having trouble finding the error...please advise. Thank you. Here is the line of code as presented in Listing 9-1, p. 280. .Range("Fx").GoalSeek Goal:=0, ChangingCell:=.Range("Cf") Here is my listing of the code subroutines associated with this example. Private Sub CommandButton1_Click() ComputeCf End Sub Public Sub ComputeCf() Dim inc As Double With Worksheets("Nonlinear Equation") inc = (.Range("Rn_2") - .Range("Rn_1")) / 20 For i = 0 To 20 .Range("Rn") = .Range("Rn_1") + (inc * i) .Range("Fx").GoalSeek Goal:=0, ChangingCell:=.Range("Cf") .Cells(10 + i, 2) = .Range("Rn") .Cells(10 + i, 3) = .Range("Cf") Next i End With End Sub Note: The following data have "cell names" defined in: Insert Name Define Rn_1 = 6000 ='Nonlinear Equation'!$C$1 Rn_2 = 12000 ='Nonlinear Equation'!$C$2 Rn = 6000 ='Nonlinear Equation'!$C$3 Cf = 0.0124 ='Nonlinear Equation'!$C$4 Fx = 3.77E-13 ='Nonlinear Equation'!$C$5 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
conversion of book bar code to ISBN | Excel Worksheet Functions | |||
Code to protect all the worksheets in a book | New Users to Excel | |||
'BeforeClose' code problems:book won't close if more than one book is open | Excel Programming | |||
Thanx David | Excel Worksheet Functions | |||
Att: David McRitchie | Excel Programming |