how to set a range in vba to be the the nonzero values in a column (skipping the zero cells)

breezer123

New Member
Joined
Jul 13, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
Hi all! Thank you for taking the time to read my question and potentially offer some help!

I am trying to use solver for an optimization problem. One of my constraints is that the cells in this range must be greater than or equal to an input cell value. However, I want the range to not include the cells that are zero, as this makes the constraint non feasible in solver. My range AN45:AN76 is changing as the solver is running. However, some of the values in the range are zero (or I can easily make them "" if this helps with the solution). The values that are zero are not always the same ones in the column, so I need to find a way to call the range to be all the cells except for the zero ones. My attempt is in this line: minrange = Range(isnot(IsEmpty("$AN$45:$AN$76"))). Thank you in advance for any advice!

VBA Code:
Sub solvermacro()

Application.ScreenUpdating = False
Application.ThisWorkbook.Sheets("Calculations").Activate

Dim minrange As Range
minrange = Range(isnot(IsEmpty("$AN$45:$AN$76")))


SolverOk SetCell:="$AN$79", MaxMinVal:=1, ValueOf:=0, ByChange:="$J$9:$J$39", _
    Engine:=1, EngineDesc:="GRG Nonlinear"
SolverAdd CellRef:="$AN$45:$AN$76", Relation:=3, FormulaText:="$J$2"
SolverAdd CellRef:="$J$9:$J$39", Relation:=1, FormulaText:="1.25"
SolverAdd CellRef:="$J$9:$J$39", Relation:=3, FormulaText:="0.75"


SolverOptions Iterations:=10, Precision:=0.1, Convergence:= _
        0.001, StepThru:=False, Scaling:=False, AssumeNonNeg:=True, Derivatives:=1
SolverOptions PopulationSize:=0, RandomSeed:=0, MutationRate:=0.075, Multistart:= _
    False, RequireBounds:=False, MaxSubproblems:=0, MaxIntegerSols:=0, IntTolerance _
    :=1, SolveWithout:=False, MaxTimeNoImp:=30

solversolve
 
End Sub
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
I have never used Solver, so am not familiar with the alerts or error types thay are generated nor their remedies. I would have to do a lot of research to figure out what is causing the error and am not willing to put that much effort into it. Maybe someone more knowledgeable about Sover will jump in and assist.
Regards, JLG
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

breezer123

New Member
Joined
Jul 13, 2020
Messages
18
Office Version
  1. 365
Platform
  1. Windows
I have never used Solver, so am not familiar with the alerts or error types thay are generated nor their remedies. I would have to do a lot of research to figure out what is causing the error and am not willing to put that much effort into it. Maybe someone more knowledgeable about Sover will jump in and assist.
Regards, JLG
No worries, thanks for your help!
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
I did notice that you have minrange in quotation marks in your SolverAdd function. variables should be used without quotation marks, otherwise they become text and not the values expected.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,145
Messages
5,623,003
Members
415,945
Latest member
Koboca

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top