"Object Required" error during Excel Solver Loop

[Lynch225]

Hey all,

First, thanks for taking the time to look at this thread. I have a written a loop that utilizes Excel Solver to calculated the best option for multiple columns. Here is my code so far:

Sub Solver2() 

Dim x As Integer 
Dim A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q As Range 
     
Sheets("Matrix").Activate 
     
Set A = Range("C377") 
Set B = Range("C21,C22,C23,C31,C32,C33,C42,C43,C44") 
Set C = Range("C11") 
Set D = Range("C35") 
Set E = Range("C12") 
Set F = Range("C46") 
Set G = Range("C20") 
Set H = Range("C21") 
Set I = Range("C24") 
Set J = Range("C10") 
Set K = Range("C31") 
Set L = Range("C32") 
Set M = Range("C42") 
Set P = Range("C43") 
Set N = Range("C6") 
Set O = Range("C5") 
    
y = 1 
   
For x = 1 To 365 
    If y = 8 Then 
        y = 0 
Else: 
            SolverOk SetCell:=A, MaxMinVal:=2, ValueOf:=0, ByChange:= _ 
            B, Engine:=1, EngineDesc:="GRG Nonlinear" 
            SolverAdd CellRef:=C, Relation:=2, FormulaText:=D 
            SolverAdd CellRef:=E, Relation:=2, FormulaText:=F 
            SolverAdd CellRef:=G, Relation:=2, FormulaText:= _ 
            H & "*'[Doc.xlsm]Menu'!$D$12" 
            SolverAdd CellRef:=I, Relation:=2, FormulaText:=J 
            SolverAdd CellRef:=K, Relation:=2, FormulaText:= _ 
            L & "*'[Doc.xlsm]Menu'!$D$12" 
            SolverAdd CellRef:=M, Relation:=2, FormulaText:= _ 
            P & "*'[Doc.xlsm]Menu'!$D$12" 
            SolverAdd CellRef:=N, Relation:=1, FormulaText:=O 
            SolverSolve userFinish = True 
        End If 
         
        Set A = A.Offset(0, 1) 
        Set B = B.Offset(0, 1) 
        Set C = C.Offset(0, 1) 
        Set D = D.Offset(0, 1) 
        Set E = E.Offset(0, 1) 
        Set F = F.Offset(0, 1) 
        Set G = G.Offset(0, 1) 
        Set H = H.Offset(0, 1) 
        Set I = I.Offset(0, 1) 
        Set J = J.Offset(0, 1) 
        Set K = K.Offset(0, 1) 
        Set L = L.Offset(0, 1) 
        Set M = M.Offset(0, 1) 
        Set N = N.Offset(0, 1) 
        Set O = O.Offset(0, 1) 
        y = y + 1 
         
    Next 
     
End Sub

Forgive the 'Else' line that seems to have a mind of its own...

Anyways, the first time, the code works great, but I receive the "Object Required" error on the line:

Set A = A.Offset(0,1)

I feel like this should be an easy fix, but does anyone have any ideas what is causing this?

Thanks!

[Rabbit]

Is this Excel VBA or VB6? Because they are different and have their own forums. Let me know which one and I'll move it if I have to.

Also, it would help to know which line caused the error.

[Lynch225]

Hi Rabbit, thanks for the reply.. I should have mentioned that it was Excel VBA, which when looking at the site should be in a different forum. My apologies.

The good news is that after doing some more research, I found out that Excel Solver is a difficult thing to automate. Apparently Excel solver does not like to use Ranges per se in setting parameters, only $A$1 style (whatever the coding term is for that) :)

I found a solution by setting my parameters as such:

y = 0

For I = 1 To 417

If (Right(Left(Columns(2 + I).Address, 4), 1)) = ":" Then
    A = Left(Columns(2 + I).Address, 3)
Else A = Left(Columns(2 + I).Address, 2)
End If
    
    If y = 7 Then
        y = 0
    ElseIf Cells(281, 2 + I) <= Cells(282, 2 + I) And Cells(59, 2 + I) <= Cells(60, 2 + I) Then
        y = y + 1
    Else
    'Loop through Solver
    SolverReset
    SolverOk SetCell:=A & B, MaxMinVal:=2, ValueOf:=0, ByChange:= _
        A & "$22:" & A & "$24," & A & "$33:" & A & "$35," & A & "$44:" & A & "$46", Engine:=1, EngineDesc:="GRG Nonlinear"
    SolverAdd CellRef:=A & "$26", Relation:=2, FormulaText:=A & "$12"
    SolverAdd CellRef:=A & "$37", Relation:=2, FormulaText:=A & "$13"
    SolverAdd CellRef:=A & "$48", Relation:=2, FormulaText:=A & "$14"
    SolverAdd CellRef:=A & "$6", Relation:=1, FormulaText:=A & "$5"
    SolverAdd CellRef:=A & "$22", Relation:=2, FormulaText:= _
        A & "$23*'[Doc.xlsm]Menu'!$D$12"
    SolverAdd CellRef:=A & "$33", Relation:=2, FormulaText:= _
        A & "$34*'[Doc.xlsm]Menu'!$D$12"
    SolverAdd CellRef:=A & "$44", Relation:=2, FormulaText:= _
        A & "$45*'[Doc.xlsm]Menu'!$D$12"
    SolverSolve userFinish = False
    SolverFinish KeepFinal:=1
    y = y + 1
    End If
 
Next

Hopefully anyone else who has similar issues with Excel Solver will find this solution helpful.

Thanks!

[Rabbit]

Glad you found a solution!

Also, I moved your thread to the Excel forum.