none
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
    '
         SolverReset
        
         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
       
        'Peak1
        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"
         
        'Peak2
        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)
           
        SolverSolve
       
        
        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
      Loop

       
    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.

    Hannah.

    Sunday, September 15, 2013 11:24 PM

All replies

  • Some things to check...
    First_
    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

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

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

    Jim Cone
    Portland, Oregon USA
    https://goo.gl/IUQUN2 (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