Solver VBA routine

[Guest]

I am trying to create a macro that will run the Excel Solver on several rows
in a spreadsheet. The number of rows could change, therefore I need to
create a loop that will work until the row or cell in the A column is empty.
You will see that I inserted the word current for the row location, I'm not
sure if this is the correct syntax.

I"m new to VBA, so I appreciate any guidance that anyone has.

Thanks,
KLM


Sub ditchdepth()
'
' ditchdepth Macro
' Macro recorded 7/31/2005 by KMANNING
'
' Keyboard Shortcut: Ctrl+k
counter = 0
Do Until Selection.Offset(counter, 0).Value = "<>"
'
SolverOk SetCell:="(current,18)", MaxMinVal:=3, ValueOf:="0",
ByChange:="(current,7)"
SolverSolve
counter = counter + 1
Loop
End Sub

 

[Dana DeLouis]

With no constraints, would Goal Seek work for you? You can usually get a
little more accuracy with Solver though.

Sub DitchDepth()
Dim R As Long 'Row
R = ActiveCell.Row
Do While Cells(R, 1) <> ""
Cells(R, 18).GoalSeek Goal:=0, ChangingCell:=Cells(R, 7)
R = R + 1
Loop
End Sub

HTH

 

[Guest]

Dana,

Thank you, that did work. GoalSeek does work in this circumstance, but
would I use the same setup if I wanted to use Solver? In other words just
insert the Solver syntax using the same types of variable?

Kris

 

[Dana DeLouis]

Hi. Yes, Solver should work. Here is an example that hopefully will get
you started:

Sub DitchDepth()
Dim R As Long 'Row

'// Solver Options here, but first...
'// Remember where we are before calling Solver!
R = ActiveCell.Row
'// Ok, now work with Solver: (May change Selection)
SolverOptions Precision:=0.000001, Convergence:=0.000001

Do While Cells(R, 1) <> vbNullString
SolverOk _
SetCell:=Cells(R, 18), _
MaxMinVal:=3, _
ValueOf:="0", _
ByChange:=Cells(R, 7)
SolverSolve True
R = R + 1
Loop 'Next (R)ow
End Sub

HTH :>)

 

[Guest]

Dana,

Thank you for your help, back to the goalseek routine. I have set up my
sheet, and everything is working, however between different sections I have a
blank row, causing solver to stop after each section. Is there a way to
specify that solver should stop after 2 blank cells/rows are found instead of
one?

Thanks