Goal Seek
 

I have a goal seek which is part of a bigger macro. The macro needs to
calculate the goal seek and then copy and paste the values 100 times
between 4 sheets. This should take not more than three minutes but of
late is failing to work. Not sure why it does not work.

Sub goal()
If Range("TotalCosts") 1 Then
Range("TargetCell1").GoalSeek goal:=Range("TargetValue1"),
ChangingCell:=Range("ChangeCell1")
Else
Range("ChangeCell1") = 0
End If
End Sub

This is what it is supposed to mean.

Costs: X

Revenue y ...... Changecell1


Profit z Profit percentage = Target cell1 which should be the
same as the Target value 1


The revenue will have to be calculated on the basis of a predefined
percentage of profit which is the target value 1 represented by the
change cell.

This should not take more than a second but it does take a long time
to come up with the value in the change cell.

Can anyone tell why it should not happen in 3 mins as it does not
happen now.

Goal Seek
 

try

with application
.EnableEvents=false
.ScreenUpdating=false
end with
' do goal seek
with application
.EnableEvents=true
.ScreenUpdating=true
end with


Fortune wrote:
I have a goal seek which is part of a bigger macro. The macro needs to
calculate the goal seek and then copy and paste the values 100 times
between 4 sheets. This should take not more than three minutes but of
late is failing to work. Not sure why it does not work.

Sub goal()
If Range("TotalCosts") 1 Then
Range("TargetCell1").GoalSeek goal:=Range("TargetValue1"),
ChangingCell:=Range("ChangeCell1")
Else
Range("ChangeCell1") = 0
End If
End Sub

This is what it is supposed to mean.

Costs: X

Revenue y ...... Changecell1


Profit z Profit percentage = Target cell1 which should be the
same as the Target value 1


The revenue will have to be calculated on the basis of a predefined
percentage of profit which is the target value 1 represented by the
change cell.

This should not take more than a second but it does take a long time
to come up with the value in the change cell.

Can anyone tell why it should not happen in 3 mins as it does not
happen now.