Run-time error '1004' : Method 'Range' of object'_Global' failed - solver vba

Question

I have written this code, but it starts having an error at the first 'SolverOk' line. Not sure how to solve and any suggestions would be appreciated!

Worksheets("Reconciliation").Activate
Range("AM3").Select
Do Until IsEmpty(ActiveCell)
    ActiveCell.Offset(0, 1).Range("A1:G1").Select
    Selection.ClearContents
    SolverReset
    SolverOk SetCell:=ActiveCell.Select, MaxMinVal:=3, ValueOf:=0, ByChange:=Range(ActiveCell.Offset(0, 1).Range("A1:G1").Select), _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverAdd CellRef:=Range("AN" & (ActiveCell.Row)).Select, Relation:=5, FormulaText:="binary"
    SolverAdd CellRef:=Range("AO" & (ActiveCell.Row)).Select, Relation:=5, FormulaText:="binary"
    SolverAdd CellRef:=Range("AP" & (ActiveCell.Row)).Select, Relation:=5, FormulaText:="binary"
    SolverAdd CellRef:=Range("AQ" & (ActiveCell.Row)).Select, Relation:=5, FormulaText:="binary"
    SolverAdd CellRef:=Range("AR" & (ActiveCell.Row)).Select, Relation:=5, FormulaText:="binary"
    SolverAdd CellRef:=Range("AS" & (ActiveCell.Row)).Select, Relation:=5, FormulaText:="binary"
    SolverAdd CellRef:=Range("AT" & (ActiveCell.Row)).Select, Relation:=5, FormulaText:="binary"
    SolverAdd CellRef:=Range("AQ" & (ActiveCell.Row)).Select, Relation:=1, FormulaText:=Range("AN" & (ActiveCell.Row)).Select
    SolverOk SetCell:=ActiveCell.Select, MaxMinVal:=3, ValueOf:=0, ByChange:=Range(ActiveCell.Offset(0, 1).Range("A1:G1").Select), _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverOk SetCell:=ActiveCell.Select, MaxMinVal:=3, ValueOf:=0, ByChange:=Range(ActiveCell.Offset(0, 1).Range("A1:G1").Select), _
        Engine:=2, EngineDesc:="Simplex LP"
    SolverSolve
    ActiveCell.Offset(1, 0).Activate
Loop

I very much doubt `SetCell:=ActiveCell.Select` is a valid parameter, since `.Select` is a procedure, not a function - it doesn't return anything, it just selects a cell. Does `SolverReset` activate or select anything? Every single `Range` call is implicitly working off the `ActiveSheet`, which makes your code extremely frail. You need to read [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/q/10714251/1188513) and pretty much rewrite everything. — Mathieu Guindon, Apr 28 '17 at 14:10

score 0 · Answer 1 · edited May 23 '17 at 10:31

Remove all the .Select calls anywhere they appear in an argument you're passing to anything:

SolverOk SetCell:=ActiveCell.Select

Should be

SolverOk SetCell:=ActiveCell

And

SolverAdd CellRef:=Range("AN" & (ActiveCell.Row)).Select

Should be

SolverAdd CellRef:=Range("AN" & (ActiveCell.Row))

You want to pass a Range object reference; .Select doesn't return anything, it selects the range, that range becomes the current Selection and it does affect the ActiveCell, which makes it nearly impossible to track exactly what's being passed to your procedures.

Avoid .Select and .Activate and work off object references instead:

Dim target As Range
Set target = ActiveCell 'best set it to an actual specific Range on a specific Worksheet.

'now work with it:
SolverOk SetCell:=target, ...

Run-time error '1004' : Method 'Range' of object'_Global' failed - solver vba

1 Answers1