| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem with Goalseek Macro
I've written a macro in Excel 2003 to perform the goalseek function, but
there's a problem. In the code below I want F66 to be equal to D4 by changing D61. If this causes D61 to go less than zero, then it gets set to zero and the second goal seek comes into play this time changing cell D56. I get an error message highlighting the second goal seek code saying error code 1004, Invalid reference. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Row = 20 And Target.Column = 4 Then Range("F66").GoalSeek Goal:=Range("D4").Value, _ ChangingCell:=Range("D61") If Range("D61").Value < 0 Then Range("D61").Value = 0 Range("F66").GoalSeek Goal:=Range("D4").Value, _ ChangingCell:=Range("D56") End If End If What am I doing wrong? Its possible that I might need to extend this to set D56 to zero if it goes under and run goalseek on another cell. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Problem with Goalseek Macro
Try this. (untested)..You can disable the events and then enable it once your
code executes.... Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Application.Intersect(Target, Range("D20") Is Nothing Then Application.EnableEvents = False Range("F66").GoalSeek Goal:=Range("D4").Value, _ ChangingCell:=Range("D61") If Range("D61").Value < 0 Then Range("D61").Value = 0 Range("F66").GoalSeek Goal:=Range("D4").Value, _ ChangingCell:=Range("D56") End If Application.EnableEvents = True End If End Sub If this post helps click Yes --------------- Jacob Skaria "Ken G." wrote: I've written a macro in Excel 2003 to perform the goalseek function, but there's a problem. In the code below I want F66 to be equal to D4 by changing D61. If this causes D61 to go less than zero, then it gets set to zero and the second goal seek comes into play this time changing cell D56. I get an error message highlighting the second goal seek code saying error code 1004, Invalid reference. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Row = 20 And Target.Column = 4 Then Range("F66").GoalSeek Goal:=Range("D4").Value, _ ChangingCell:=Range("D61") If Range("D61").Value < 0 Then Range("D61").Value = 0 Range("F66").GoalSeek Goal:=Range("D4").Value, _ ChangingCell:=Range("D56") End If End If What am I doing wrong? Its possible that I might need to extend this to set D56 to zero if it goes under and run goalseek on another cell. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Problem with Goalseek Macro
Thanks Jacob.
This line .. If Not Application.Intersect(Target, Range("D20") Is Nothing Then .... is giving a syntax error. "Jacob Skaria" wrote: Try this. (untested)..You can disable the events and then enable it once your code executes.... Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Application.Intersect(Target, Range("D20") Is Nothing Then Application.EnableEvents = False Range("F66").GoalSeek Goal:=Range("D4").Value, _ ChangingCell:=Range("D61") If Range("D61").Value < 0 Then Range("D61").Value = 0 Range("F66").GoalSeek Goal:=Range("D4").Value, _ ChangingCell:=Range("D56") End If Application.EnableEvents = True End If End Sub If this post helps click Yes --------------- Jacob Skaria "Ken G." wrote: I've written a macro in Excel 2003 to perform the goalseek function, but there's a problem. In the code below I want F66 to be equal to D4 by changing D61. If this causes D61 to go less than zero, then it gets set to zero and the second goal seek comes into play this time changing cell D56. I get an error message highlighting the second goal seek code saying error code 1004, Invalid reference. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Row = 20 And Target.Column = 4 Then Range("F66").GoalSeek Goal:=Range("D4").Value, _ ChangingCell:=Range("D61") If Range("D61").Value < 0 Then Range("D61").Value = 0 Range("F66").GoalSeek Goal:=Range("D4").Value, _ ChangingCell:=Range("D56") End If End If What am I doing wrong? Its possible that I might need to extend this to set D56 to zero if it goes under and run goalseek on another cell. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Problem with Goalseek Macro
I fixed the syntax error but its still giving the same 1004 error message at
the same place saying the reference is invalid... Range("F66").GoalSeek Goal:=Range("D4").Value, _ ChangingCell:=Range("D56") "Jacob Skaria" wrote: Try this. (untested)..You can disable the events and then enable it once your code executes.... Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Application.Intersect(Target, Range("D20") Is Nothing Then Application.EnableEvents = False Range("F66").GoalSeek Goal:=Range("D4").Value, _ ChangingCell:=Range("D61") If Range("D61").Value < 0 Then Range("D61").Value = 0 Range("F66").GoalSeek Goal:=Range("D4").Value, _ ChangingCell:=Range("D56") End If Application.EnableEvents = True End If End Sub If this post helps click Yes --------------- Jacob Skaria "Ken G." wrote: I've written a macro in Excel 2003 to perform the goalseek function, but there's a problem. In the code below I want F66 to be equal to D4 by changing D61. If this causes D61 to go less than zero, then it gets set to zero and the second goal seek comes into play this time changing cell D56. I get an error message highlighting the second goal seek code saying error code 1004, Invalid reference. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Row = 20 And Target.Column = 4 Then Range("F66").GoalSeek Goal:=Range("D4").Value, _ ChangingCell:=Range("D61") If Range("D61").Value < 0 Then Range("D61").Value = 0 Range("F66").GoalSeek Goal:=Range("D4").Value, _ ChangingCell:=Range("D56") End If End If What am I doing wrong? Its possible that I might need to extend this to set D56 to zero if it goes under and run goalseek on another cell. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Problem with Goalseek Macro
The problem appeared to be that D56 contained a formula. I changed the macro
to do the calculation in D56 so that goalseek sees it is a value and it now works. "Jacob Skaria" wrote: Try this. (untested)..You can disable the events and then enable it once your code executes.... Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Application.Intersect(Target, Range("D20") Is Nothing Then Application.EnableEvents = False Range("F66").GoalSeek Goal:=Range("D4").Value, _ ChangingCell:=Range("D61") If Range("D61").Value < 0 Then Range("D61").Value = 0 Range("F66").GoalSeek Goal:=Range("D4").Value, _ ChangingCell:=Range("D56") End If Application.EnableEvents = True End If End Sub If this post helps click Yes --------------- Jacob Skaria "Ken G." wrote: I've written a macro in Excel 2003 to perform the goalseek function, but there's a problem. In the code below I want F66 to be equal to D4 by changing D61. If this causes D61 to go less than zero, then it gets set to zero and the second goal seek comes into play this time changing cell D56. I get an error message highlighting the second goal seek code saying error code 1004, Invalid reference. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Row = 20 And Target.Column = 4 Then Range("F66").GoalSeek Goal:=Range("D4").Value, _ ChangingCell:=Range("D61") If Range("D61").Value < 0 Then Range("D61").Value = 0 Range("F66").GoalSeek Goal:=Range("D4").Value, _ ChangingCell:=Range("D56") End If End If What am I doing wrong? Its possible that I might need to extend this to set D56 to zero if it goes under and run goalseek on another cell. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Problem with Goalseek Macro
Ken , good to hear it is working..
1. I missed thhe closing braces ... 2. The below line will make sure that the target is D20... If Not Application.Intersect(Target, Range("D20")) Is Nothing Then If this post helps click Yes --------------- Jacob Skaria "Ken G" wrote: The problem appeared to be that D56 contained a formula. I changed the macro to do the calculation in D56 so that goalseek sees it is a value and it now works. "Jacob Skaria" wrote: Try this. (untested)..You can disable the events and then enable it once your code executes.... Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Not Application.Intersect(Target, Range("D20") Is Nothing Then Application.EnableEvents = False Range("F66").GoalSeek Goal:=Range("D4").Value, _ ChangingCell:=Range("D61") If Range("D61").Value < 0 Then Range("D61").Value = 0 Range("F66").GoalSeek Goal:=Range("D4").Value, _ ChangingCell:=Range("D56") End If Application.EnableEvents = True End If End Sub If this post helps click Yes --------------- Jacob Skaria "Ken G." wrote: I've written a macro in Excel 2003 to perform the goalseek function, but there's a problem. In the code below I want F66 to be equal to D4 by changing D61. If this causes D61 to go less than zero, then it gets set to zero and the second goal seek comes into play this time changing cell D56. I get an error message highlighting the second goal seek code saying error code 1004, Invalid reference. Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Row = 20 And Target.Column = 4 Then Range("F66").GoalSeek Goal:=Range("D4").Value, _ ChangingCell:=Range("D61") If Range("D61").Value < 0 Then Range("D61").Value = 0 Range("F66").GoalSeek Goal:=Range("D4").Value, _ ChangingCell:=Range("D56") End If End If What am I doing wrong? Its possible that I might need to extend this to set D56 to zero if it goes under and run goalseek on another cell. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Using Excel Solver or Goalseek on multiple Sheets in a Workbook au | Excel Worksheet Functions | |||
| How do I set a goalseek value via an input box using vba macro | Excel Worksheet Functions | |||
| Goalseek Excel 2007 | Excel Discussion (Misc queries) | |||
| GoalSeek in VBA | Excel Discussion (Misc queries) | |||
| Goalseek / Solver Target Value | Excel Worksheet Functions | |||
![Ken G.[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
