Excel VBA command

[GalileoG]

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.

 

[IRstuff]

You can use the macro recorder to see what the VBA code would look like

TTFN

FAQ731-376
Chinese prisoner wins Nobel Peace Prize

 

[TDAA]

Range("BK39").FormulaR1C1 = "1"

 

[davidbeach]

I think that

Range("BK39").value=1

would also work and is a few characters shorter.

 

[IDS]

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

http://newtonexcelbach.wordpress.com/