×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Excel VBA command

Excel VBA command

Excel VBA command

(OP)
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.

RE: Excel VBA command

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

RE: Excel VBA command

I think that

Range("BK39").value=1

would also work and is a few characters shorter.

RE: Excel VBA command

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/
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources