Name argument not found

Question

When I am running the below code the error "Name argument not found" is coming for SetCell:="$I$3".

Sub Simple_Exponential_Smoothing()
    Dim x As Integer
    Dim lastrow As Integer
    Application.ScreenUpdating = False
    Sheets("COV_0.2").Cells(1, 49).Value = "MAD"
    Sheets("COV_0.2").Cells(1, 50).Value = "Alpha"
    lastrow = Sheets("COV_0.2").Cells(Rows.Count, 2).End(xlUp).Row
    For x = 2 To lastrow
        If Sheets("COV_0.2").Cells(x, 31).Value <> "S4" Then
            Range("G" & x & ":AD" & x).Select
            Selection.Copy
            Sheets("Analysis_Constant").Select
            Range("C4").Select
            Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
                SkipBlanks:=False, Transpose:=True
            Application.Run "Solver.xlam!SolverReset"

   '***ERROR ON THE FOLLOWING LINE: `Name argument not found`
            Application.Run "Solver.xlam!SolverOk", SetCell:="$I$3", _
                MaxMinVal:=2, ValueOf:=0, ByChange:="$G$1", Engine:=3, _
                EngineDesc:="Evolutionary"

            Application.Run "Solver.xlam!SolverAdd", CellRef:="$G$1", _
                Relation:=1, FormulaText:="0.9"
            Application.Run "Solver.xlam!SolverAdd", CellRef:="$G$1", _
                Relation:=3, FormulaText:="0.1"
            SolverSolve True

Try running with the arguments in order but without the arg descriptors. — QHarr, Jun 05 '18 at 05:10
There's probably not a file named: `Solver.xlam!SolverOk` or `Solver.xlam!SolverAdd` — ashleedawg, Jun 05 '18 at 05:10
Or record with a macro to check syntax. You should also have checked the reference to Solver in tools > references — QHarr, Jun 05 '18 at 05:11
To expand on @QHarr's comment: you can't pass named arguments with `Application.Run` - you can only pass by position. — Tim Williams, Jun 05 '18 at 06:04

score 0 · Answer 1 · answered Jun 05 '18 at 06:22

You must set a reference to the Solver addin in Tools/References:

Select References from the Tools menu in the Excel VBA editor window.
Check the "Solver" checkbox.
Click OK.

And then the correct Solver syntax would be:

SolverReset

SolverOk SetCell:="$I$3", _
    MaxMinVal:=2, ValueOf:=0, ByChange:="$G$1", Engine:=3, _
    EngineDesc:="Evolutionary"

SolverAdd CellRef:="$G$1", Relation:=1, FormulaText:="0.9"
SolverAdd CellRef:="$G$1", Relation:=3, FormulaText:="0.1"
SolverSolve True

Additionally I recommend not to use Integer for row counting because Excel has more rows than Integer can handle. It is recommended always to use Long instead of Integer in VBA.
And you might want to have a look into How to avoid using Select in Excel VBA to make your code more reliable, shorter, more stable and a lot faster.

Name argument not found

1 Answers1