Power Query Course in Spanish
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

Some videos you may like

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
  •