VBA - questions

KevinZ

New Member
Joined
Apr 14, 2019
Messages
33
First, I have the code below. When I run this, a new spreadsheet is created as expected. When the data is placed on the new spreadsheet, it puts all of the selected values that I am trying to solve for (A2 to A134) instead of just the solver results, 6 of the values as determined by
Solveradd CellRef:="$L$9", Relation:=2, FormulaText:="=$L$8" where $L$8 is set value to 6. ie, when solving, the number of values to use must equal 6. This has worked running solver but is now not working as expected in VBA.

Any idea what I have done wrong?

Second, My output is going into a single column. I would like the solved results, 6 items per loop, to go to a single row then the next solve to go directly below that.

Any suggestions on how I can do that?


Option Explicit
Sub TestSolve()
'

' TestSolve Macro


'Reset Solver
SolverReset

'Create New Spreadsheet - Named "Export"
Worksheets.Add after:=Sheets("Parameters")
ActiveSheet.Name = "Export"
Worksheets("Parameters").Activate

'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


'Set Constraints

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"



Dim List_Current_row As Long
List_Current_row = Worksheets("Export").Cells(Rows.Count, 1).End(xlUp).Row + 1

If i = 0 Then
Worksheets("Export").Cells(List_Current_row, 1).Resize(1, 6).Value = _
Worksheets("Parameters").Range("C2:C201").Value
Else
Worksheets("Export").Cells(List_Current_row, 1).Resize(200, 1).Value = _
Worksheets("Parameters").Range("C2:C201").Value
End If

SolverSolve False
Next i

End Sub
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

KevinZ

New Member
Joined
Apr 14, 2019
Messages
33
Closing this thread, posting a new thread to hopefully clarify what I am looking to do.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,244
Members
416,963
Latest member
samfuge

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top