Loop question

[KevinZ]

I am trying to create a loop that uses a variable as the end number. This number can range from 1 to 200. Below is what I created. I have tried a variety of variations but I cannot figure out how to get this to work. Any Ideas?

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

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

 

[Fluff]

Is this what you want

Sub KevinZ()
   Dim StartNumber As Long, EndNumber As Long, i As Long
   StartNumber = 1
   EndNumber = Range("L11").Value
   For i = StartNumber To EndNumber
      'do something
   Next i
End Sub

 

[KevinZ]

Thank you. I assume the 'do something will be my constraints.

 

[joeu2004]

Please note a subtle, but important change in Fluff's suggestion: change type Integer to Long.

Even though the expected range of your numbers fit within the limits of type Integer (even Byte!), generally it is "good practice" to use type Long for all integer variables. In modern computers, there is no benefit to using type Integer, other than memory usage; and that matters only when you are allocating huge arrays. More importantly, the "bad habit" of using type Integer (or Byte) will eventually get you into trouble when you don't expect it. For example, 32767 + 10 - 100 results in an overflow error, even though the result is less than 32767.

Also, if your For loop always starts from 1, there might be no need for the StartNumber (unless you have other uses for it). Simply write:

For i = 1 to EndNumber

 

[KevinZ]

Thank you for the advice.

 

[Fluff]

Thank you. I assume the 'do something will be my constraints.

It's whatever you need to do in the loop, for instance

Range("A"&i).value=i

 

[KevinZ]

Thanks again.

 

[Fluff]

You're welcome

 

[KevinZ]

I have set up the code as above. I have tried both suggestions but am getting a Runtime error 1004 message when I try to execute it. It says Application defined or object defined error. This is the code as I am trying to run it right now. It does create a new spreadsheet but then I get the error.

Any ideas?

Option Explicit
Sub TestSolve()
'
' TestSolve Macro
'Steps
' 1. Add solution to spreadsheet
' 2. Usage - Number of time to use each variable

'Reset Solver
SolverReset

'Create New Spreadsheet - Named "Export"
Worksheets.Add after:=Sheets("Parameters")
ActiveSheet.Name = "Export"
'Loop - Number of times to run - 1 to EndNumber
Dim StartNumber As Long, EndNumber As Long, i As Long

StartNumber = 1
EndNumber = Range("L11").Value
' For i = StartNumber To EndNumber
For i = 1 To EndNumber
' Add Constraints here

'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"
Next i

'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

' Add results to Export Spreadsheet
' Max value lower each loop. New "$L$4 < Old $L$4

End Sub

 

[KevinZ]

Note: I recently added OpenSolver since I was running into Solver size issues. Not sure if that would have any effect.