VBA Code for Goal Seek to Run Automatically
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: VBA Code for Goal Seek to Run Automatically

  1. #1
    New Member
    Join Date
    Jan 2010
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Code for Goal Seek to Run Automatically

    I have a couple of worksheets that have goal seek already performed on 20 target cells (for each worksheet). I did this manually, without a macro (since I'm absolutely horrible at all of this).

    Now from what I can tell, I need a code to add to the worksheet module in order to make sure that the goal seek updates itself whenever I change a value in the formula of the target cell.

    what IS that code?! I can't understand the codes some people have put up, so I have no idea what to do.

    Target Cell: M21:M42
    Changing Cells: N21: N42

  2. #2
    New Member
    Join Date
    Jan 2010
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code for Goal Seek to Run Automatically

    Okay I have a code but it doesn't work. What's wrong with it?

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim bSuccess As Boolean
    On Error Resume Next
    bSuccess = Range("M21:M42").GoalSeek(0, Range("N21:N42"))
    On Error GoTo 0
    If Not bSuccess Then
    MsgBox "Goal Seek Failed for Cell ""N21:N42""!"
    End If
    End Sub

    I know it has something to do with the ranges but I don't know how to change it.

  3. #3
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,651
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA Code for Goal Seek to Run Automatically

    Try

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("M21:M42")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Target.GoalSeek goal:=0, changingcell:=Target.Offset(, 1)
    Application.EnableEvents = True
    End Sub
    HTH, Peter
    Please test any code on a copy of your workbook.

  4. #4
    New Member
    Join Date
    Jan 2010
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code for Goal Seek to Run Automatically

    That didn't work

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

DMCA.com