Excel 2007 Goal Seek Formula

Hello,

Please review the attached code.  The goal seek formula is making the adjustment in cell E64.  I need to set up a limit in which the amount in E64 that goal seek recommends should not be less than 0 or greater than the amount in cell I64.  If the amount does not fall within this parameter an error message appears stating "Amount Does Not Fall Within Required Parameters.  Please Try Again".

Any suggestions on how to make this happen?

Thanks!


Range("E74").GoalSeek Goal:=Range("H68"), ChangingCell:=Range("E64")

Open in new window

LVL 1
EscanabaAsked:
Who is Participating?
 
TommySzalapskiConnect With a Mentor Commented:
Then you'd need something like this (and the median trick isn't as useful).
Range("E74").GoalSeek Goal:=Range("H68"), ChangingCell:=Range("E64")

If Range("E64").Value <0 Then
  MsgBox "Came out less than 0, changed to 0"
  Range("E64").Value = 0
End if
If Range("E64").Value > Range("I64").Value Then
  MsgBox "Came out greater than " & Range("I64").Value & ", changed to " & Range("I64").Value
  Range("E64").Value = Range("I64").Value
End If

Open in new window

0
 
TommySzalapskiCommented:
Range("E64").Value = WorksheetFunction.Median(0, Range("E64").Value, Range("I64").Value)
0
 
EscanabaAuthor Commented:
So for the entire string its this:
Range("E74").GoalSeek Goal:=Range("H68"), ChangingCell:=Range("E64").Value = WorksheetFunction.Median(0, Range("E64").Value, Range("I64").Value)

If so its bugging stating reference not valid.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
EscanabaAuthor Commented:
Here is the full code:

Private Sub CommandButton2_Click()
    With Application
       .DisplayAlerts = False
       .EnableEvents = False
       .ScreenUpdating = False
      Application.Calculation = xlCalculationManual
    End With
   
     
    Range("E74").GoalSeek Goal:=Range("H68"), ChangingCell:=Range("E64").Value = WorksheetFunction.Median(0, Range("E64").Value, Range("I64").Value)
   
   
    With Application
    .DisplayAlerts = True
    .EnableEvents = True
    .ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    End With
 
End Sub
0
 
TommySzalapskiCommented:
No. My code was for after the value was put in E64. Like this: (You could use a different cell for the final value)
Range("E74").GoalSeek Goal:=Range("H68"), ChangingCell:=Range("E64")

Range("E64").Value = WorksheetFunction.Median(0, Range("E64").Value, Range("I64").Value)

Open in new window

0
 
byundtCommented:
Solver can handle multiple constraints and changing cells, but Goal Seek requires a single criterion (minimum, maximum or specific value).

From the description of the problem, the final solution might be:
=MAX(0,MIN(I64,E64))                 the Goal Seek result should be between 0 and I64.

You would then run Goal Seek using E64 same as previously, then replace the value found by Goal Seek with the result of the MAX/MIN formula.
0
 
TommySzalapskiCommented:
byundt, your answer is the same as mine except the median function does the min/max in one step.
0
 
EscanabaAuthor Commented:
Got it & thank you.  Any recommendations on the 2nd part of my original question regarding generating an error message so the end user knows they've exceeded the parameters?  As it stands, nothing updates which is good but doesnt tell them why.
0
 
EscanabaAuthor Commented:
Appreciate the assistance.  Thank you.
0
All Courses

From novice to tech pro — start learning today.