Need Excel Goal Seek help

[Swarup Modak]

In the attached file I have created a calculation.
The problem is that the calculation is being held very slowly within do while loop. I would like to use Goal Seek in place of do while loop. I have gone several sites to understand Goal Seek function.

In the macro I have written one goal seek function as follows:

' To Generate Residual Value in Special Allowance
      If .Worksheets(lcCalcSheet).Range("$" + "CP" + "$" + CStr(lnLoopStartRow)).Value > 0 Then
         .Worksheets(lcCalcSheet).Range("$" + "CP" + "$" + CStr(lnLoopStartRow)).GoalSeek _
            Goal:=0, _
            ChangingCell:=.Worksheets(lcCalcSheet).Range("$" + "AP" + "$" + CStr(lnLoopStartRow))
      End If

I am facing two problems:
1. After goal seek program is reading value at cell CP6 and marking error though in excel I am getting zero value (zero value is expected).

2. I am also unable to replace further do while loop by goal seek for faster calculation.

Help is requested.

Any suggestion for faster calculation other than using goal seek is also welcome.

With Best Regards,
______________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[Hui]

So you want CP6 = 0 but which is the input cell?

 

[Swarup Modak]

At the outset I thank you very much for your reply.
I am sorry for late reply due to illness.

I had a do while...loop as follows:

      Do While .Worksheets(lcCalcSheet).Range("$" + "CP" + "$" + CStr(lnLoopStartRow)).Value > 0
         .Worksheets(lcCalcSheet).Range("$" + "AP" + "$" + CStr(lnLoopStartRow)).Value = .Worksheets(lcCalcSheet).Range("$" + "AP" + "$" + CStr(lnLoopStartRow)).Value + 1
      Loop

Instead of the aforesaid do while...loop I have used GoalSeek as follows:

      If .Worksheets(lcCalcSheet).Range("$" + "CP" + "$" + CStr(lnLoopStartRow)).Value > 0 Then
         .Worksheets(lcCalcSheet).Range("$" + "CP" + "$" + CStr(lnLoopStartRow)).GoalSeek _
            Goal:=0, _
            ChangingCell:=.Worksheets(lcCalcSheet).Range("$" + "AP" + "$" + CStr(lnLoopStartRow))
      End If

Hence change is being made in the AP column. Goal is to decrease the value of CP column upto ZERO .

Now my requirement is as follows:

I have the following do while...loop as follows:

      Do While .Worksheets(lcCalcSheet).Range("$" + "CP" + "$" + CStr(lnLoopStartRow)).Value < 0 And .Worksheets(lcCalcSheet).Range("$" + "AR" + "$" + CStr(lnLoopStartRow)).Value > 0
         ' Special
         .Worksheets(lcCalcSheet).Range("$" + "AQ" + "$" + CStr(lnLoopStartRow)).Value = .Worksheets(lcCalcSheet).Range("$" + "AQ" + "$" + CStr(lnLoopStartRow)).Value + 1
      Loop

Hence change is to be made in the AQ column. Goal is to decrease the value of CP column upto ZERO subject to the value of AR column greater than ZERO.

I am unable to apply GoalSeek function due to existence the additional condition of AR column.

Hope the requirement is now clear to you.

With Best Regards,