Run-time Error 1004 - Application-defined or Object-defined error RRS feed

  • Question

  • Hi, I am getting a run-time error '1004': Application defined or object defined error, in excel 2010.

    I dont know much about writing macros at all and I did not write the coding for this. I have been sent it to use for some data processing for my research work.

    I think the problem is that this code was written in excel 2007. Is there a simple way to change the code so it is compatible with 2010?

    The code is:

    Sub FitTwoGauss1()
    ' FitTwoGauss1 Macro
    ' To fit two gauss peaks
    ' Keyboard Shortcut: Ctrl+q
         SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.00001, AssumeLinear _
            :=False, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _
            IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=False

        'x = 10
        'y = 21
        x = Range("F688")
        y = Range("F689") + 1
      Do While x < y
        'This line set the cell to optimise and the cells to change
        SolverOK SetCell:=Cells(669, x), MaxMinVal:=3, ValueOf:=0, ByChange:=Range(Cells(672, x), Cells(677, x))
        'SolverOK SetCell:=Cells(669, x), MaxMinVal:=2, ByChange:=Range(Cells(672, x), Cells(677, x))
       'Set up constraints initially  3 means greater than, 1 means less than
        SolverAdd CellRef:=Cells(672, x), Relation:=3, FormulaText:="$E$683"
        SolverAdd CellRef:=Cells(672, x), Relation:=1, FormulaText:="$F$683"
        SolverAdd CellRef:=Cells(673, x), Relation:=3, FormulaText:="$E$684"
        SolverAdd CellRef:=Cells(673, x), Relation:=1, FormulaText:="$F$684"
        SolverAdd CellRef:=Cells(674, x), Relation:=3, FormulaText:="$E$685"
        SolverAdd CellRef:=Cells(675, x), Relation:=3, FormulaText:="$I$683"
        SolverAdd CellRef:=Cells(675, x), Relation:=1, FormulaText:="$J$683"
        SolverAdd CellRef:=Cells(676, x), Relation:=3, FormulaText:="$I$684"
        SolverAdd CellRef:=Cells(676, x), Relation:=1, FormulaText:="$J$684"
        SolverAdd CellRef:=Cells(677, x), Relation:=3, FormulaText:="$I$685"
        SolverAdd CellRef:=Cells(675, x), Relation:=3, FormulaText:=Cells(678, x)
        Range("K691") = x
        'Gets rid of constraints
        SolverDelete CellRef:=Cells(672, x), Relation:=3
        SolverDelete CellRef:=Cells(672, x), Relation:=1
        SolverDelete CellRef:=Cells(673, x), Relation:=3
        SolverDelete CellRef:=Cells(673, x), Relation:=1
        SolverDelete CellRef:=Cells(674, x), Relation:=3
        SolverDelete CellRef:=Cells(675, x), Relation:=3
        SolverDelete CellRef:=Cells(675, x), Relation:=1
        SolverDelete CellRef:=Cells(676, x), Relation:=3
        SolverDelete CellRef:=Cells(676, x), Relation:=1
        SolverDelete CellRef:=Cells(677, x), Relation:=3
        x = x + 1

    End Sub

    The issue is in the "SolverOK SetCell:=Cells(669, x), MaxMinVal:=3, ValueOf:=0, ByChange:=Range(Cells(672, x), Cells(677, x))" line I believe??

    Any suggestions or help would be hugely appreciated.


    Sunday, September 15, 2013 11:24 PM

All replies

  • Some things to check...
    In the VBE where you have placed the code, on the menubar:  Tools | References...
    There should be a checkmark by the item: "Solver"
    If it is not there, click the browse button and find the Solver folder under the Library folder and open Solver.xlam

    Also, add these two lines just above "SolverReset"...
    Dim x As Long
    Dim y As Long

    On the worksheet, there must be a numeric value in cells F688 and F689

    Jim Cone
    Portland, Oregon USA (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 11:18 PM
    Monday, September 16, 2013 12:20 AM