VBA Solver Loop Problem

[cockneyjosh]

Hi there,

I have a probably quite simple problem but I am still trying to get to grips with using Solver in VBA and I am struggling to get this formula to work.

Basically I have a table with 5 Columns and 6 rows and I want to make it so that the numbers in the 3rd column are such that the final column equals zero.

So far my code is

Sub Macro2()
'
' Macro2 Macro
'


'
For x = 22 To 27
SolverOk SetCell:="$I$x", MaxMinVal:=3, ValueOf:=0, ByChange:="$G$x", Engine _
:=1, EngineDesc:="GRG Nonlinear"
SolverOk SetCell:="$I$x", MaxMinVal:=3, ValueOf:=0, ByChange:="$G$x", Engine _
:=1, EngineDesc:="GRG Nonlinear"
SolverSolve userFinish:=False

Next x
End Sub


But for some reason the solver returns really large numbers and I get an error message saying that the objective cell values do not converge? In addition is there a way to stop the dialog box from coming up so I don't have to click ok 5 times?

Here is the table if that helps


[TABLE="width: 500"]
<tbody>[TR]
[TD]-161.87[/TD]
[TD]2437.5[/TD]
[TD]Variable Cell[/TD]
[TD][/TD]
[TD]=E22+F22-G22[/TD]
[/TR]
[TR]
[TD]-1005.62[/TD]
[TD]2437.5[/TD]
[TD]Variable Cell[/TD]
[TD][/TD]
[TD]=E23+F23-G23[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]etc[/TD]
[TD][/TD]
[TD]etc[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Thanks for your help,

Josh

 

[sergioMabres]

Hi Josh
Probably I am not getting the whole problem but have you consider using a formula for the 3rd column, like =-SUM(G22:H22,J22:K22)
Then L22=SUM(G22:K22) will always be 0 for all values and all rows, and you would also avoid circular calculations?

https://dl.dropboxusercontent.com/u/23094164/****neyjosh.jpg

Cheers
Sergio

 

[cockneyjosh]

Hi Sergio,

Thanks, that does get the desired result although I would like to be able to do it in VBA so that I can have a button that calculates the amounts paid after I change some of the numbers in the table.
Do you know how I could do this?

Thanks for your help

Josh

 

[sergioMabres]

I do not see any "Amount Paid", in general if cells have no recursive/circular and formulas do all the work there is to be done you will not need for VBA
Cheers
Sergio