VBA Code : Example 9-1 David Bourg's Book

[Guest]

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

 

[JE McGimpsey]

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.

 

[Guest]

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