VBA Questions - New to Macros

[KevinZ]

Sorry for the long post, any help is appreciated.

I am working on the Macro below. I am hoping someone can provide a little guidance on a few questions.

1. Should the Marco be in Module1, The spreadsheet I am running it against or in ThisWorkbook?
2. I can run the solver without an issue, When I try to run the exact same thing in the Macro, I get a Compile error on the SolverReset function. Is there something I need to change?
3. When I create my loop, do I need to loop to the start of the constraints or is there a better way to do this?
4. Is there a good resource for me to learn about putting the results on the new spreadsheet in separate rows for each loop?
5. I want to set a maximum number of times for each value to be used in the results. Is there a good resource for me to learn how to do this?

Option Explicit
Sub TestSolve()
'
'Should this be in Module1, The spreadheet I am running it against or ThisWorkbook?
' TestSolve Macro
'Steps
' 1. Reset Solver
' 2. Create New Spreadsheet
' 3. Load Constraints
' 4. Add solution to spreadsheet
' 5. Loop - Does it need to start at Load Constraints? If not, where?

'Reset Solver - Getting Compile Error Sub or Function not defined
SolverReset

'Create New Spreadsheet - Named "Export"

'Set Constraints
'Note: Cell numbers to be changed to Column Names ($A$2:$A$134 changed to "Use") once columns are named

SolverOk SetCell:="$L$4", MaxMinVal:=1, ValueOf:=0, ByChange:="$A$2:$A$134", _
Engine:=1, EngineDesc:="GRG Nonlinear"
Solveradd CellRef:="$A$2:$A$134", Relation:=5, FormulaText:="binary"
Solveradd CellRef:="$L$10", Relation:=1, FormulaText:="$=L$5"
Solveradd CellRef:="$L$10", Relation:=3, FormulaText:="=$L$6"
Solveradd CellRef:="$L$9", Relation:=2, FormulaText:="=$L$8"
Solveradd CellRef:="$G$2:$G$134", Relation:=2, FormulaTest:="=$L$7"

'Need to set a maximum usage for each line.
' (Row 2($A$2) - 5 times, Row 3($A$3) - 6 times, row 4($A$4) - 2 times, etc)
' Can be a number or a percentage of times run (10%, 15%)
SolverSolve False

'Number of times to run - 1 to EndNumber
Dim StartNumber As Integer
Dim EndNumber As Integer

For StartNumber = 1 To EndNumber
EndNumber CellRef:="=$L$11"
Next StartNumber

End Sub

Thank you,
Kevin

 

[MARK858]

2. I can run the solver without an issue, When I try to run the exact same thing in the Macro, I get a Compile error on the SolverReset function. Is there something I need to change?

Where do you have the code currently? in the sheet module?

Is Solver checked in Tools - References?

 

[KevinZ]

The code is currently in Module1. Tools-References is not available on tihs currently.

 

[MARK858]

Tools-References is not available on tihs currently.

What do you mean Tools - References is not available on this currently

 

[MARK858]

What do you mean Tools - References is not available on this currently

If you mean Tools - References is grayed out are you sure that you aren't still in Break mode?

 

[KevinZ]

When I go to Tools, References is greyed out.

 

[MARK858]

Maybe you didn't see my last post, are you still in break mode? If yes then click Run - Reset.

 

[KevinZ]

Thank you, That fixed that issue.