Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Solver VBA routine
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 |
#2
|
|||
|
|||
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 -- Dana DeLouis Win XP & Office 2003 "KLM" wrote in message ... 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 |
#3
|
|||
|
|||
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" wrote: 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 -- Dana DeLouis Win XP & Office 2003 "KLM" wrote in message ... 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 |
#4
|
|||
|
|||
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 :) -- Dana DeLouis Win XP & Office 2003 "KLM" wrote in message ... 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" wrote: 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 -- Dana DeLouis Win XP & Office 2003 "KLM" wrote in message ... 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 |
#5
|
|||
|
|||
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 "Dana DeLouis" wrote: 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 :) -- Dana DeLouis Win XP & Office 2003 "KLM" wrote in message ... 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" wrote: 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 -- Dana DeLouis Win XP & Office 2003 "KLM" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using solver with function with multiple outputs | Excel Worksheet Functions | |||
Solver and dynamic ranges | Excel Worksheet Functions | |||
Solver Problems | Excel Worksheet Functions | |||
Excel: Solver | Excel Worksheet Functions | |||
solver constraint | Excel Worksheet Functions |