View Full Version : Solver in Userform Linked in Excel
prilirp
10-30-2019, 11:18 PM
I am trying to find out how to use solver in vba userform that is linked to an excel file with Worksheets("LookupLists")
Dim vInput As String
vInput = InputBox("Object WD:")
SolverOk SetCell:=["Q35"], 3, vInput, ["Q36"], 1, "GRG Nonlinear"
SolverSolve True
All that appears is "Syntax Error"
Please help, completely new on this.
Thanks
Is that worksheet in the same workbook as the UserForm?
Even if so, you still need a reference to the sheet. Note that I have never used Solver, so everything except the Worksheet reference in just a guess on my part.
Dim ws As Worksheet
Set ws = worksheets("LookupLists")
With ws
SolverOk SetCell:=["Q35"], 3, vInput, ["Q36"], 1, "GRG Nonlinear"
etc
etc
You might need to ensure that the workbook is open and that the worksheet exists before you get that far.
Artik
11-01-2019, 05:41 PM
The syntax error is probably due to an incorrect entry of the cell reference. More correctly when the LookupLists worksheet is active:
SolverOk [Q35], 3, vInput, [Q36], 1, "GRG Nonlinear"
If you are not sure that the LookupLists worksheet is active, use the With ... End With construct:
With Worksheets("LookupLists")
SolverOk SetCell:=.[Q35], MaxMinVal:=3, ValueOf:=vInput, ByChange:=.[Q36], Engine:=1, EngineDesc:="GRG Nonlinear"
End WithIn addition, calling the function requires either the names of all parameters to be entered or none of them.
Also check that you have a reference to the Solver library https://docs.microsoft.com/en-us/office/vba/excel/concepts/functions/using-the-solver-vba-functions
Artik
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.