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

 

[joeu2004]

First, it would help if you tell what statement is causing the runtime error. Sometimes, that is "obvious", if you have the option to click a Debug button. But often, VBA does not give us any clue.

If that's the case, you have some options. First, you can press f9 to set breakpoints on statements. Second, you can press f8 to step through each statement. But those methods can be tedious, especially in a loop. (Although in this case, I suspect the runtime error will occur immediately.)

Alternatively, make the following code changes.

Sub TestSolve()
On Error GoTo oops
[....]
Exit Sub

oops:
Stop
Resume
End Sub

The runtime error will send you to the Stop statement. From there, press f8 twice to execute the Stop and Resume statements. The debugger will return to the statement that causes the runtime error.

At that point, the mistake might be "obvious" after you look at the faulty statement and perhaps some dependent values.

If not, at least now you can tell use which statements cause the runtime error.

 

[joeu2004]

Second, your loop does not make sense to me as written. But perhaps the key is the comment, which I do not understand: ``Cell numbers to be changed to Column Names ($A$2:$A$134 changed to "Use") once columns are named``.

The point is: when you write a loop, something must change each iteration. Often, what changes depends on "i", the loop control variable. But sometimes, its purpose is simply to force the code to be re-execute "n" times. ("n" is EndNumber in your design.)

The change might be subtle. For example, perhaps you intend for SolverSolve to be re-executed "n" times, relying on the fact that each execution changes the cell that Solver modifies.

But if that is the case, SolverSolve should be inside the loop, not outside. That is, it should be before the ``Next i`` statement.

On the other hand, returning to the comment that I do not understand, perhaps your intent is for SolverAdd to add a number of different constraints in each iteration.

If that is the case, I suspect that the variable "i" should be incorporated into the cell/range references. But I have no idea what your intention is.

Of course, this will matter only after you resolve your runtime error.

 

[KevinZ]

I stepped through the code. It executes Through the SolverSolve statement before I get the error. I tried adding your code but I could not get it to work.

For the second item, That is a note for me to name the columns once I get everything working. This is because the number of rows of data will vary every week and I don't want to have to change the code to match the number of rows. So in that example, The thing that changes is the values in

$A$2:$A$134. Once I have the code working properly, I will name the field "Use" or something similar because the number of rows will be anywhere between 30 and approx. 200 on a weekly basis. ​

 

[joeu2004]

I stepped through the code. It executes Through the SolverSolve statement before I get the error.

Google "excel vba solversolve" without quotes, and you will find https://docs.microsoft.com/en-us/office/vba/excel/concepts/functions/solversolve-function. It explains:

``SolverSolve( UserFinish, ShowRef)
UserFinish Optional Variant. True to return the results without displaying the Solver Results dialog box. False or omitted to return the results and display the Solver Results dialog box. ShowRef Optional Variant. You can pass the name of a macro (as a string) as the ShowRef argument.``

So the runtime error arises because you pass False to ShowRef. I suspect you intended to pass False to UserFinish. Note that you wrote ``SolverSolve comma False``. Presumably, you want to write ``SolverSolve False`` (no comma) or simply SolverSolve (default UserFinish).

The thing that changes is the values in $A$2:$A$134

I don't believe so, not in the code as written thus far. Perhaps it's a work-in-progress. But as written, nothing happens in the loop other than set up Solver constraints. So I see no reason to expect that the values of A2:A134 change.

Again, I suspect that you want SolverSolve to be inside the loop, if you expect that the operation of Solver causes the values of A2:A134 to change.

That said, forgive me if I'm speaking out-of-school. You really have not told us much about what the loop should accomplish, so I really don't know what I'm talking about.

PS.... Frankly, I don't want to know what the loop should accomplish. That is, I do not want to help you design your code. I'm happy to answer usage questions, like the ones that you have asked. I only need to know design details to the extent that it might help answer your usage questions. So I probably should not offered an opinion about the loop design. Mea culpa!

 

[KevinZ]

I appreciate the direction. I am teaching myself how to use VBA and picked something I was interested in. I havn't written code in a very long time so it is learning all over again. I will play around with the design some more. I still have a few things I am working through to finish my code.

 

[KevinZ]

Thanks for the help on the loop, I did have the Next i in the worng place.