Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations cowski on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel VBA command

Status
Not open for further replies.

GalileoG

Structural
Joined
Feb 17, 2007
Messages
467
Location
CA
Hi all,

I have the following VBA code which performs a goal seek whenever a value has changed in my spreadsheet. However, what I would like to do is set value BK39 to equal 1.0 PRIOR to performing the goal seek (in order for goal seek to determine the smallest root for my equation). What would I need to add to this code? Thank you!

Private Sub Worksheet_Change(ByVal Target As Range)

Dim bSuccess As Boolean

On Error Resume Next
bSuccess = Range("BH39").GoalSeek(0, Range("BK39"))
On Error GoTo 0
If Not bSuccess Then
MsgBox "Goal Seek Failed for Cell ""BK39""!"
End If

End Sub



Clansman

If a builder has built a house for a man and has not made his work sound, and the house which he has built has fallen down and so caused the death of the householder, that builder shall be put to death." Code of Hammurabi, c.2040 B.C.
 
Range("BK39").FormulaR1C1 = "1"
 
I think that

Range("BK39").value=1

would also work and is a few characters shorter.
 
Range("BK39")=1
or [BK39] = 1

will also work, but Range("BK39").value=1 is probably best for self-documentation purposes.

Also it's a good idea to use range names rather than cell addresses, so that if you move the ranges, or insert or delete rows or columns, you don't have to change the macro.

Doug Jenkins
Interactive Design Services
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top