Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Solver Problem
Sorry for this post, I'm pretty new to both Excel and
Visual Basic. I'm trying to automate a solver call in Visual Basic, but I keep getting error messages. One says "Set Target Cell must be a single cell on the active sheet" with only an "OK" button on it. However, when I hit OK, the solver doesn't compute anything, just gives me a zero. This error appears everytime I run the script. I tried recording my actions with a macro, then copied and pasted the code from that macro into that of another, larger VB macro. Here is the cod I'm using: ' totalcols is the number of total columns temp = = Sheets("sheet5").Range(Cells(2, 5),_ Cells(totalcols, 5)).Address Cells(totalcols + 2, 5).Select SolverOk SetCell:="cells(totalcols+2,5)", MaxMinVal:=1,_ ValueOf:="0", ByChange:=" & temp & " SolverAdd CellRef:="cells(totalcols+1,5)", Relation:=1,_ FormulaText:="cells(totalcols+1,4)" SolverOk SetCell:="cells(totalcols+2,5)", MaxMinVal:=1,_ ValueOf:="0", ByChange:=" & temp & " SolverAdd CellRef:="cells(totalcols+3,5)", Relation:=1,_ FormulaText:="cells(totalcols+3,4)" SolverOk SetCell:="cells(totalcols+2,5)", MaxMinVal:=1,_ ValueOf:="0", ByChange:=" & temp & " SolverSolve When I execute this code from a macro by itself and without the variables (the original recorded macro without the variables) I get a different error: every time you run solver, an option pops up asking if you want to "Keep Solver Solution" or "Restore Original Values". Above this, there is a message: "Error in model. Please verify that the cells and constraints are valid". Here is that code: Range("E10").Select SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_ ByChange:="$E$2:$E$8" SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_ ByChange:="$E$2:$E$8" SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_ ByChange:="$E$2:$E$8" SolverAdd CellRef:="$E$9", Relation:=1, FormulaText:="$D$9" SolverAdd CellRef:="$E$11", Relation:=1,_ FormulaText:="$D$11" SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_ ByChange:="$E$2:$E$8" SolverOptions MaxTime:=100, Iterations:=100,_ Precision:=0.000001, AssumeLinear _ :=False, StepThru:=False, Estimates:=1, Derivatives:=1,_ SearchOption:=1, _ IntTolerance:=5, Scaling:=False, Convergence:=0.0001_, AssumeNonNeg:=True SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_ ByChange:="$E$2:$E$8" SolverSolve If I just recorded my use of the solver into a macro, why then is it having problems duplicating the results? I can go back and manually run solver again without errors. Am I doing something wrong with the way I'm coding this? Thanks in advance. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Another Solver Problem
Hello. I can't really follow, so here is just a general idea.
This part of your code is listed multiple times, so I think you are doing something wrong with your translation from the macro recorder to your own version. SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_ ByChange:="$E$2:$E$8" SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_ ByChange:="$E$2:$E$8" SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_ ByChange:="$E$2:$E$8" Also, in this part of your code... SolverOk SetCell:="cells(totalcols+2,5)", MaxMinVal:=1,_ ValueOf:="0", ByChange:=" & temp & " This is listed multiple times also. There really should be only 1 "SolverOK" per problem. Also, your code for the particular cell includes "cells(totalcols+2,5)". This is a string that does not hold anything meaningful to Solver. It is expecting an Address. This is a string that does not look like an Address. If you wish to use Cells(...), then here is an idea. SolverOk SetCell:=Cells(totalcols+2,5).Address, MaxMinVal:=1, ByChange:= Range("Temp").Address (No need for ValueOf) Anyway, this is just quick and dirty. Nothing tested, but maybe something to get you going. HTH. -- Dana DeLouis Using Windows XP & Office XP = = = = = = = = = = = = = = = = = "new user" wrote in message ... Sorry for this post, I'm pretty new to both Excel and Visual Basic. I'm trying to automate a solver call in Visual Basic, but I keep getting error messages. One says "Set Target Cell must be a single cell on the active sheet" with only an "OK" button on it. However, when I hit OK, the solver doesn't compute anything, just gives me a zero. This error appears everytime I run the script. I tried recording my actions with a macro, then copied and pasted the code from that macro into that of another, larger VB macro. Here is the cod I'm using: ' totalcols is the number of total columns temp = = Sheets("sheet5").Range(Cells(2, 5),_ Cells(totalcols, 5)).Address Cells(totalcols + 2, 5).Select SolverOk SetCell:="cells(totalcols+2,5)", MaxMinVal:=1,_ ValueOf:="0", ByChange:=" & temp & " SolverAdd CellRef:="cells(totalcols+1,5)", Relation:=1,_ FormulaText:="cells(totalcols+1,4)" SolverOk SetCell:="cells(totalcols+2,5)", MaxMinVal:=1,_ ValueOf:="0", ByChange:=" & temp & " SolverAdd CellRef:="cells(totalcols+3,5)", Relation:=1,_ FormulaText:="cells(totalcols+3,4)" SolverOk SetCell:="cells(totalcols+2,5)", MaxMinVal:=1,_ ValueOf:="0", ByChange:=" & temp & " SolverSolve When I execute this code from a macro by itself and without the variables (the original recorded macro without the variables) I get a different error: every time you run solver, an option pops up asking if you want to "Keep Solver Solution" or "Restore Original Values". Above this, there is a message: "Error in model. Please verify that the cells and constraints are valid". Here is that code: Range("E10").Select SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_ ByChange:="$E$2:$E$8" SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_ ByChange:="$E$2:$E$8" SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_ ByChange:="$E$2:$E$8" SolverAdd CellRef:="$E$9", Relation:=1, FormulaText:="$D$9" SolverAdd CellRef:="$E$11", Relation:=1,_ FormulaText:="$D$11" SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_ ByChange:="$E$2:$E$8" SolverOptions MaxTime:=100, Iterations:=100,_ Precision:=0.000001, AssumeLinear _ :=False, StepThru:=False, Estimates:=1, Derivatives:=1,_ SearchOption:=1, _ IntTolerance:=5, Scaling:=False, Convergence:=0.0001_, AssumeNonNeg:=True SolverOk SetCell:="$E$10", MaxMinVal:=1, ValueOf:="0",_ ByChange:="$E$2:$E$8" SolverSolve If I just recorded my use of the solver into a macro, why then is it having problems duplicating the results? I can go back and manually run solver again without errors. Am I doing something wrong with the way I'm coding this? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem Solver | Excel Worksheet Functions | |||
Solver problem | Excel Worksheet Functions | |||
problem with solver | Excel Discussion (Misc queries) | |||
Interesting Solver problem (Solver encounters an error) | Excel Discussion (Misc queries) | |||
IF problem using Solver | Excel Worksheet Functions |