Macro to run solver repeatedly

[sofiaventura]

Hello guys!
So I recorded a macro to run solver at least one time, but I keep getting an error that the solver could not find any solution, but when I perform this by hand without any macro the solver works just fine.
Besides that my goal is to have a macro to perform solver for many cells.
You can see in the attached file what I'm trying to accomplish. The macro 1 contain my goal for the first case.
I ask solver to equal the equations in the rows H and I to zero by changing the values of the cells in the rows J and K.
Could anyone try to write the right code please?
Thank you
exemplo.xlsm

[sofiaventura]

By the way I'm using excel 2010 :-)

[shg]

Sub sv()
    Dim cell        As Range

    SolverReset

    For Each cell In Columns("H").SpecialCells(xlCellTypeFormulas)
        With Rows(cell.Row)
            .Range("L1").FormulaR1C1 = "=sumsq(rc[-4]:rc[-3])"
            SolverOk setcell:=.Range("L1"), _
                     MaxMinVal:=2, _
                     ByChange:=.Range("J1:K1").Address, _
                     Engine:=1
            SolverSolve UserFinish:=True
        End With
    Next cell
End Sub

[Alf]

http://www.excelforum.com/excel-prog...le-memory.html

You could use the macro I wrote in post #7 for a model just change the column letters.

Alf

[sofiaventura]

Thanks for all the replies in advance.
Shg with your macro I still get a equations cells with different values than zero.
Alf, know I don´t need to run the solver to the entire columns, but just a few cells that matches the last day of every month. I want a new macro to avoid wasting time that excel needs to perform solver for all the rows.

[sofiaventura]

I forgot to mention that with the Alf macro, there are a few equations cells that don't change the value to zero too.

[shg]

Shg with your macro I still get a equations cells with different values than zero.

That's true -- Solver can't solve those manually, either.

[Alf]

Making a copy of sheet Plan1 as Plan2 then running both shg’s (sv macro) and my (Sol_Loop) macro and finally hiding all rows where range H:I was empty (HideRow macro) I was surprised to find some unusual differences between the two results.

I do wonder why?

Alf

[sofiaventura]

Solver works in mysterious ways.
I think I found a better way to solver work and to minimize time.
First, like Shg I added a extra column with the sum of the modules of the cells in columns H and I. Then when I performed solver I ask (first case):
Goal Seek: L24 to min
Changing the cells: J24 and K 24
Restrict to : H24 and I 24= 0
In Plan 1 I only put the rows at the end of wich month, to facilitate the macro.
Can you please try to right a macro with the new conditions? (for plan 1)
The row 132 is the last one, I just put the first ones and the last ones just to show you what I'm gonna do.
Solver_exemplo_mod.xlsm

[shg]

Goal Seek: L24 to min
Changing the cells: J24 and K 24
Restrict to : H24 and I 24= 0

That's redundant, considering that L is the sum of the squares of H and I.

[sofiaventura]

So far, this way I can get better results than just with the restrictions H24 and I 24 =0.