Solver in VBA - variable "Bychange" value

Lejora


I started by programming a simple loop which identifies the cells which I want SOLVER to calculate. Let the variable VAR1="$H$9;$H$10;$H$11;$H$12;$H$13"

Then I try to execute the code:

SolverOk SetCell:="$K$1", MaxMinVal:=3, ValueOf:="0", ByChange:=VAR1

However, it does not work. If I write Bychange:=VAR1, VBA does not assume this.

Another problem. If I write:

SolverAdd CellRef:="$H$9H$18", Relation:=3, Formulatext:="0.1"
or
SolverAdd CellRef:="$H$9H$18", Relation:=3, Formulatext:="0,1"

when i open solver, it has assumed FORMULATEXT= 1 ($H$9H$18>=1) instead of 0.1

Can somewone help me with this

Lejora


Microsoft Excel is in Portuguese, but somehow VBA is in english, despite the fact that the instalation CD's are from the original portuguese version.

Jon Peltier

You hinted at the answer when you mentioned the regional setting (Portuguese language). This also includes settings such as currentcy format, thousands separator, decimal indicator, and series separator. Use commas, not semicolons, to separate the cell addresses in the ByChanging string:

VAR1 = "$H$9,$H$10,$H$11,$H$12,$H$13"

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

Lejora

Hi! Thank you very much for your reply. Actually, the problem subsisted even after changing to commas. I uninstalled Office 2003 and reinstalled it, with all options for Excel and VBA checked, and now it works (don't ask me why...). The VBA help menu didn't contain information about SOLVER and now it does, also.

Still, when I write SolverAdd CellRef:="$H$1" , Relation:=3, FormulaText:="0,1", the value for Formulatext becomes 1. It doesn't matter if I write 0,1 or 0.1. It always becomes 1 (I can see it when I open the SOLVER menu). If I start recording a macro and fill the SOLVER menu, VBA writes Formulatext="0,1""", with 3 times " at the end. Even that does not work, because if I run the macro, SOLVER will fill the lower boundary condition with the number 1. I find this quite strange... I've been meaning to try this in an all english version of excel, but still haven't had that chance.

Any hints on how to solve this
Thanks in advance.