0

One of the equations in a Solver model that I am creating in an Excel VBA macro is as follows:

SolverAdd CellRef:="$M$18:$M$30", Relation:=2, FormulaText:="$N$18:$N$30"

How could I implement it so that the row number "18" is a value that can be read as an input from another cell? For example, cell A1 may have the value 18, which is what I want used in this equation. If the value in A1 was 10, then the equation should read as:

SolverAdd CellRef:="$M$10:$M$30", Relation:=2, FormulaText:="$N$10:$N$30"

Similarly, the variables are defined as:

SolverOk SetCell:="$H$47", MaxMinVal:=2, ValueOf:=0, ByChange:="$H$18:$J$30,$R$18:$R$30", _
    Engine:=1, EngineDesc:="Simplex LP"

Again, when the value of the number in cell A1 changes from, say 18 to 10, the variables should change from "$H$18:$J$30,$R$18:$R$30" to "$H$10:$J$30,$R$10:$R$30".

I will greatly appreciate any suggestions.

Thanks a lot.

Teamothy
  • 2,000
  • 3
  • 16
  • 26
naren
  • 1
  • 3
    Have you tried using the Add-on SolverStudio? It is really useful. However, If what you want is to have a variable as input use this: `input_variable = .Cells(1,1)` or `input_variable = Range("A1")` and change to `"$M"&input_variable&":$M$30` – danieltakeshi Aug 15 '17 at 01:31
  • Awesome! This works. Thanks Daniel. – naren Aug 16 '17 at 03:49

1 Answers1

3
  • To have a variable as input use this: input_variable = .Cells(1,1) or input_variable = Range("A1") and change to "$M"&input_variable&":$M$30

Refer to this question for further ways: VBA: Selecting range by variables or Range as a Variable VBA

  • Another alternative is to use SolverStudio, it is really useful, because you model in Excel using almost all famous optmisation libraries.

As problems grows, Excel seems to be unable to solve some problems and there are many other professional softwares that uses those optimisation modelling languages.

If you try to install some problems can happen, enter this link to solve: Help Page My problem was different and was solved here.

In my classes I was taught in AMPL, so programming with SolverStudio was easier than Excel VBA, because SolverStudio's languages are focused on optimisation problems.

danieltakeshi
  • 887
  • 9
  • 37