Hello,
First post ever on this forum, so I apologize if I'm doing in incorrectly...
I should also preface this with saying I'm very new to VBA, and have only really used it a handful of times in the past.
I'm looking to run a set of solver trials, each trial having a Goal, Value, and Changed Cell in a row. The solver run is to do this for quite a lot of rows.
Here is the code I'm using, but it gives me a Compile error: Invalid qualifier statement then highlights the .Address portion of "ValueOf:=cellValue.Address(True, True).Value"
Any suggestions on how to get around this?
Sub solveAll()
'
' Solver Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Dim cellChange As Range
Dim cellGoal As Range
Dim cellValue As Range
Set cellChange = ActiveSheet.Range("V4:V243")
Set cellGoal = ActiveSheet.Range("U4:U243")
Set cellValue = ActiveSheet.Range("S4:S243")
Do '*****LOOP & Solve*****
SolverReset
SolverOk SetCell:=cellGoal.Address(True, True), MaxMinVal:=3, ValueOf:=cellValue.Value, ByChange:=cellChange.Address(True, True)
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
Set cellChange = cellChange.Offset(1, 0)
Set cellGoal = cellGoal.Offset(1, 0)
Set cellValue = cellValue.Offset(1, 0)
Loop While Trim(cellGoal.Text) <> "" 'until goal cell is empty
End Sub
EDIT:
After some tinkering, I've figured it out. I'll post my code here so others may be able to use it in the future. It was my initial Set cellChage/cellGoal/cellValue statements that were shown as ranges rather than just a single cell that caused the issue. Here is the code I'm using and it seems to work perfectly:
Sub solveAll()
'
' Solver Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Dim cellChange As Range
Dim cellGoal As Range
Dim cellValue As Range
Set cellChange = Range("V4:V4")
Set cellGoal = Range("U4:U4")
Set cellValue = Range("S4:S4")
Do '*****LOOP & Solve*****
SolverReset
SolverOptions Precision:=0.001
SolverOk SetCell:=cellGoal.Address(True, True), MaxMinVal:=3, ValueOf:=cellValue.Value, ByChange:=cellChange.Address(True, True)
SolverSolve UserFinish:=True
SolverFinish KeepFinal:=1
Set cellChange = cellChange.Offset(1, 0)
Set cellGoal = cellGoal.Offset(1, 0)
Set cellValue = cellValue.Offset(1, 0)
Loop While Trim(cellGoal.Text) <> "" 'until goal cell is empty
End Sub
The macro runs as long as there is something (usually 0 by default if my cellChange column is set to 0) in my cellGoal column. This can therefore be used for varying lengths of data.
Bookmarks