×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Problem with solver constraints

Problem with solver constraints

Problem with solver constraints

(OP)
Can anyone help with this solver VBA code? basically it refuses to set the constraint for cells(106,3+chkcount) to 1
****solveradd cellref:=Sheet1.Cells(106, 3 + chkcount), relation:=3, formulatext:=1***

If I change this to zero it works but not 1... Doesn anyone know why? I thought it was becasue the cells were zero and that would make it illegal so I changed em to one before the line is done but still it doesn't add the constraint.



If CheckBox1.Value = True Then
        total = TextBox1
        Sheet1.Cells(116, 3) = TextBox1
        For chkcount = 1 To Sheet2.Cells(20, 9)
            Sheet1.Cells(106, 3 + chkcount) = 1
            solveradd cellref:=Sheet1.Cells(106, 3 + chkcount), relation:=3, formulatext:=1
            
            If chkcount = 1 Then
                solveradd cellref:=Sheet1.Cells(106, 3 + chkcount), relation:=1, formulatext:=100 / TextBox1
                'solverchange cellref:=Sheet1.Cells(106, 3 + chkcount), relation:=1, formulatext:=100 / TextBox1
            Else
                solveradd cellref:=Sheet1.Cells(106, 3 + chkcount), relation:=1, formulatext:=Sheet1.Cells(106, 3 + chkcount - 1)
                solverchange cellref:=Sheet1.Cells(106, 3 + chkcount), relation:=1, formulatext:=Sheet1.Cells(106, 3 + chkcount - 1)
            End If
            
            
            
        Next chkcount
        solveradd cellref:=Sheet1.Cells(106, 3), relation:=3, formulatext:=0
        'solverchange cellref:=Sheet1.Cells(106, 3), relation:=3, formulatext:=0

RE: Problem with solver constraints

(OP)
I don't understand what is actually being done with the code shown, are you saying that solver gets confused between sheets?

RE: Problem with solver constraints

(OP)
OK I have re-done my code, Im sorry but I didn't understand what was happening with what was suggested by yourself on the other page, this is my new code. It works perfectly but for some reason when I make a few changes to the cells within this same segemnt of code, solver just sits there but reports that everythin is "optimal" also the solver reset doesn't seem to be working propperly at the end. My brain is really starting to hurt now!

Cells(106, 3) = TextBox2
        yearcount = TextBox2
        preasureyr = "R111C12:R111C" & 11 + TextBox2
        totalyrs = "R111C11:R111C" & 11 + (TextBox2 * 2)
        
        SolverOk SetCell:="R108C34", MaxMinVal:=2, ValueOf:="0", ByChange:=totalyrs
        SolverAdd CellRef:="R111C11", Relation:=3, FormulaText:="0"
    
        SolverOk SetCell:="R108C34", MaxMinVal:=2, ValueOf:="0", ByChange:=totalyrs
        SolverAdd CellRef:=preasureyr, Relation:=1, FormulaText:="R32C3"
    
        SolverOk SetCell:="R108C34", MaxMinVal:=2, ValueOf:="0", ByChange:=totalyrs
 
       
        SolverSolve UserFinish:=False
        Sheets("Sheet2").Select
        Application.ScreenUpdating = True
        solverreset

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources