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!
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")
TommySzalapski
Range("E64").Value = WorksheetFunction.Median(0
ASKER
So for the entire string its this:
Range("E74").GoalSeek Goal:=Range("H68"), ChangingCell:=Range("E64")
If so its bugging stating reference not valid.
Range("E74").GoalSeek Goal:=Range("H68"), ChangingCell:=Range("E64")
If so its bugging stating reference not valid.
ASKER
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")
With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End With
End Sub
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")
With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End With
End Sub
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)
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.
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.
byundt, your answer is the same as mine except the median function does the min/max in one step.
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Appreciate the assistance. Thank you.