Macro not exiting correctly
 

The following macro was working to solve for face. But in case the solver
couldn't determine the face amount - I wanted to handle this error message.

Solver can get the correct face amount - however it will still display the
message box. What am I doing wrong??



Sub Solve_for_Face()
Dim cntr As Integer
If shtSolve.Range("z19").Value < 5000 Then
shtSolve.Range("z19").Value = 5000
End If
shtSolve.Range("aa25").GoalSeek Goal:=shtSolve.Range("aa26"), _
ChangingCell:=shtSolve.Range("z19")

Do
On Error GoTo newprem
shtSolve.Range("z19").Value = Round(shtSolve.Range("z19").Value, 3)
+ 0.001

Loop Until shtSolve.Range("aa25").Value shtSolve.Range("aa26").Value
shtSolve.Range("z19").Value = shtSolve.Range("z19").Value - 0.001
shtSummary.Range("UnitAmt") = Int(shtSolve.Range("z19") * 1000)
shtSummary.Range("UnitADB") = Int(shtSolve.Range("z21") * 1000)
Exit Sub
newprem:
MsgBox "cannot solve for face enter different premium"
End Sub

Macro not exiting correctly
 

I would remove the On Error statement to see what really is causing the
error. make the On Error statement a comment by putting a single quote in
front of the line. Then run the code to find the cause of the error.

"Brad" wrote:

The following macro was working to solve for face. But in case the solver
couldn't determine the face amount - I wanted to handle this error message.

Solver can get the correct face amount - however it will still display the
message box. What am I doing wrong??



Sub Solve_for_Face()
Dim cntr As Integer
If shtSolve.Range("z19").Value < 5000 Then
shtSolve.Range("z19").Value = 5000
End If
shtSolve.Range("aa25").GoalSeek Goal:=shtSolve.Range("aa26"), _
ChangingCell:=shtSolve.Range("z19")

Do
On Error GoTo newprem
shtSolve.Range("z19").Value = Round(shtSolve.Range("z19").Value, 3)
+ 0.001

Loop Until shtSolve.Range("aa25").Value shtSolve.Range("aa26").Value
shtSolve.Range("z19").Value = shtSolve.Range("z19").Value - 0.001
shtSummary.Range("UnitAmt") = Int(shtSolve.Range("z19") * 1000)
shtSummary.Range("UnitADB") = Int(shtSolve.Range("z21") * 1000)
Exit Sub
newprem:
MsgBox "cannot solve for face enter different premium"
End Sub

Macro not exiting correctly
 

There are situations where an individual can input a premium that is not
sufficient for the minimum face amount.

The macro correctly comes up with the correct face amount when a valid
premium is inputted - however it still show the message box.

"Brad" wrote:

The following macro was working to solve for face. But in case the solver
couldn't determine the face amount - I wanted to handle this error message.

Solver can get the correct face amount - however it will still display the
message box. What am I doing wrong??



Sub Solve_for_Face()
Dim cntr As Integer
If shtSolve.Range("z19").Value < 5000 Then
shtSolve.Range("z19").Value = 5000
End If
shtSolve.Range("aa25").GoalSeek Goal:=shtSolve.Range("aa26"), _
ChangingCell:=shtSolve.Range("z19")

Do
On Error GoTo newprem
shtSolve.Range("z19").Value = Round(shtSolve.Range("z19").Value, 3)
+ 0.001

Loop Until shtSolve.Range("aa25").Value shtSolve.Range("aa26").Value
shtSolve.Range("z19").Value = shtSolve.Range("z19").Value - 0.001
shtSummary.Range("UnitAmt") = Int(shtSolve.Range("z19") * 1000)
shtSummary.Range("UnitADB") = Int(shtSolve.Range("z21") * 1000)
Exit Sub
newprem:
MsgBox "cannot solve for face enter different premium"
End Sub

Macro not exiting correctly
 

The only way the message box can appear on an error because of the exit sub
statement. therefore, you must have an error to get the message box.

If you don't want to remove the On Error statement. Then you can break the
code by pressing Ctrl Break when the message box incorrectly appears. Then
look at the values in Z19 to give a clue to the source of the problem.

"Brad" wrote:

There are situations where an individual can input a premium that is not
sufficient for the minimum face amount.

The macro correctly comes up with the correct face amount when a valid
premium is inputted - however it still show the message box.

"Brad" wrote:

The following macro was working to solve for face. But in case the solver
couldn't determine the face amount - I wanted to handle this error message.

Solver can get the correct face amount - however it will still display the
message box. What am I doing wrong??



Sub Solve_for_Face()
Dim cntr As Integer
If shtSolve.Range("z19").Value < 5000 Then
shtSolve.Range("z19").Value = 5000
End If
shtSolve.Range("aa25").GoalSeek Goal:=shtSolve.Range("aa26"), _
ChangingCell:=shtSolve.Range("z19")

Do
On Error GoTo newprem
shtSolve.Range("z19").Value = Round(shtSolve.Range("z19").Value, 3)
+ 0.001

Loop Until shtSolve.Range("aa25").Value shtSolve.Range("aa26").Value
shtSolve.Range("z19").Value = shtSolve.Range("z19").Value - 0.001
shtSummary.Range("UnitAmt") = Int(shtSolve.Range("z19") * 1000)
shtSummary.Range("UnitADB") = Int(shtSolve.Range("z21") * 1000)
Exit Sub
newprem:
MsgBox "cannot solve for face enter different premium"
End Sub

Macro not exiting correctly
 

Okay you're right
Removing the On Error statement I get a
"Run=time error -2177417848(80010108)
Method "hidden" of object 'range' failed.

Not what I expected - haven't had this error ever before. need to do some
more looking

"Joel" wrote:

The only way the message box can appear on an error because of the exit sub
statement. therefore, you must have an error to get the message box.

If you don't want to remove the On Error statement. Then you can break the
code by pressing Ctrl Break when the message box incorrectly appears. Then
look at the values in Z19 to give a clue to the source of the problem.

"Brad" wrote:

There are situations where an individual can input a premium that is not
sufficient for the minimum face amount.

The macro correctly comes up with the correct face amount when a valid
premium is inputted - however it still show the message box.

"Brad" wrote:

The following macro was working to solve for face. But in case the solver
couldn't determine the face amount - I wanted to handle this error message.

Solver can get the correct face amount - however it will still display the
message box. What am I doing wrong??



Sub Solve_for_Face()
Dim cntr As Integer
If shtSolve.Range("z19").Value < 5000 Then
shtSolve.Range("z19").Value = 5000
End If
shtSolve.Range("aa25").GoalSeek Goal:=shtSolve.Range("aa26"), _
ChangingCell:=shtSolve.Range("z19")

Do
On Error GoTo newprem
shtSolve.Range("z19").Value = Round(shtSolve.Range("z19").Value, 3)
+ 0.001

Loop Until shtSolve.Range("aa25").Value shtSolve.Range("aa26").Value
shtSolve.Range("z19").Value = shtSolve.Range("z19").Value - 0.001
shtSummary.Range("UnitAmt") = Int(shtSolve.Range("z19") * 1000)
shtSummary.Range("UnitADB") = Int(shtSolve.Range("z21") * 1000)
Exit Sub
newprem:
MsgBox "cannot solve for face enter different premium"
End Sub