Solver - Macro Error. This thing is killing me!

[Mightyginger]

Hi all,

I have been trying to automate Solver in VBA with mixed success. The idea is that I want to loop through multiple rows. Each row has a range of variables to be changed and another cell has the net difference to be solved to zero. My issue is that once I hit the set number of iterations a dialogue box entitled "Macro Error" pops up which then goes on to say "[SOLVER.XLA]Excel4Functions!R20C1". Interestingly I tried removing the interations but it still seems to be keeping them. I think I need to be deleting the solver and restarting it but will that ultimately help?

My code:

Sub beginSolving()
Dim i As Integer
i = 1
'Fill with swap formulas
Application.ScreenUpdating = True
Application.DisplayAlerts = True
For i = 7 To 8
SolverOk Cells(i, 169).Address, 3, 0, Range(Cells(i, 96), Cells(i, 112)).Address
'SolverOptions Iterations:=20 ', StepThru:=False
SolverSolve UserFinish:=True
solverFinish KeepFinal:=1

Next i
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

I'd really appreciate your help. Happy to send spreadsheet etc.


Neil.

 

[Mightyginger]

Bump please.